rolion2019-09-24 17:19:47
rolion, 2019-09-24 17:19:47

Again about caching queries to the database, how to do it?

VPS. Website. Server side - PHP and MySQL. There is a site search. After entering 2 characters, a query is made to the database and the results are immediately uploaded under the search string. When entering more than 2 characters, the search was already done on the client side from what came from the database in order to avoid unnecessary queries.
With the client part, everything is good, everything suits the speed. But the query to the database does not suit, about 2 seconds is a long time, the user will not type two characters first, then wait, then the rest.
The search goes through different tables: categories, subcategories, products, users, etc. Along the way, queries are made to related tables, for example, countries, cities, etc., so that later users, in the results of satisfying rows, substitute the name of the country and city, for example.
Standard queries:

$resultCat = $mysqli->query("SELECT `id`,`title` FROM `cat` WHERE `title` LIKE '%".$query."%' ORDER BY `title`");

For related posts:
$resultCityList = $mysqli->query("SELECT `id`,`title` FROM `city` ORDER BY `title`");// Вытаскиваем все города

Then, all this is collected in a heap and given to the AJAX client.
It turns out that there are records that can be cached for a month, some for a day, some for 15 minutes, some for 5 minutes.
How to do it better, please advise:
1) Somehow cache the entire result of all requests for a specific $query value. Those. if we take into account that the user will enter in Russian and these are 2 characters, then I think there will be less than 1000 possible results.
2) Cache each request separately with an indication of the period.
Well, the most interesting question: what kind of caching is best suited here?
Will MySQLCache help, like
$resCity = $mysqli->query("SELECT SQL_CACHE `id`,`id_country`,`title` FROM `city` ORDER BY `title`");

Or what caching would be the most efficient? I didn’t touch this at all ...
I would be immensely grateful for advice and help!

Answer the question

In order to leave comments, you need to log in

3 answer(s)
FanatPHP, 2019-09-24

We remember the second main programming rule: if it works slowly, then you don’t need to cache anything. It is necessary to make everything work quickly without any cache.

ThunderCat, 2019-09-24

first: WHERE `title` LIKE '%".$query."%'- remove the full wildcard, LIKE '".$query."%'it will work faster and use the index, unlike the first option. You do not need to choose "Kaliningrad" by "grad".
The second - indexes are longer on text fields, where it is placed in a varchar - make a varchar with an index. Then most likely the cache is not needed.

Lazy @BojackHorseman PHP, 2019-09-24

without delving into the intricacies of the implementation ...
it will be more profitable to cache the result by query) caching efficiency itself is calculated as %% cache hit.
but the effect of the cache / its absence on user metrics should not be allowed. everything should work without the cache

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question