I need to display the most viewed news for the last week. The request is working, but it takes about a minute. The database has more than one and a half million entries.

How can I optimize a query, can there be an option to use another query?

The index is on all fields. Index city_url - unique items 599 index time - unique items 1633941 index views - unique items 15

SELECT * FROM news WHERE city_url like '$city' AND time > unix_timestamp(date_sub(current_date, interval 1 week)) ORDER BY views DESC limit 3
  • one
    What does "index on all fields" mean, is it a single index or a lot? If one, then in what order are the fields in it? If for each column, then you hopefully know that only one index can be used in a single query for one table. And many indexes slow down work. What do you have in the $ city variable? if there is a mask and it starts with the % sign, then the index cannot be used. And show the execution plan of the query, which is obtained by the explain command and from which to start any optimization - Mike
  • Fulfilled explain: dl3.joxi.net/drive/2018/12/21/0025/0454/1659334/34 / ... It is strange that in phpmyadmin the query was executed very quickly, and the site can run a minute, or maybe quickly. - Stop-TussiN
  • The plan to put it mildly is strange. I'm afraid he can run across the table in descending order views. I do not understand why he took such an order. Specifically, for this request, an index of two fields (city_url, time) would be optimal and write order by city_url, views DESC that it could not use an index on views. However, this index is best to delete at all, 1/15 of the table for it is still a huge amount and you are unlikely to perform the search where views = X, so it is not applicable in any cases and only hurts this particular one - Mike
  • This is of course provided that you are always looking for a city on strict equality, i.e. Do not use a mask. And what it would explicitly indicate would be to replace the like with = in the request, this can also confuse the optimizer - Mike
  • one
    1. city_url LIKE 'moskva' is a horror. Rewrite on city_url = 'moskva' . 2. Try ALTER TABLE bezf ADD KEY idx_c(time, city_url, views); then EXPLAIN SELECT ... and exhaust here. To correctly measure the speed, SELECT SQL_NO_CACHE ... - there will be an “honest” time, and not how much muscle climbed into the cache. What time was "before" optimization, and "after"? 3. Stop throwing shots, issue in the form of text. To do this easily, use the muscle console ( mysql ... ) - Total Pusher

0