SQL query optimization, why is index not used?

Question

MySQL

I support the movie search engine site, the site displays the last 25 search queries, search queries are saved in the database as a mark of the last search if there are links to the desired movie.

mysql database structure approximate
id int(11) Auto Increment
name varchar(100) []
last_searched datetime [0000-00-00 00:00:00]
featured tinyint(1) [0]

Indexes on all fields, for the benefit of read requests 96 %.

Querying the last 25 searches is very heavy
# [email protected]: letme[letme] @ localhost []
# Query_time: 7.261724 Lock_time: 1.596558 Rows_sent: 25 Rows_examined: 43940

SELECT id, name FROM movie ORDER BY last_searched DESC, featured DESC LIMIT 25;

How to find out if a query uses indexes?
Is it possible to optimize without rewriting the logic of the site (the site is not mine, so I would just put the search queries in a separate table and take the last 25 by id)?

Answer 1

I apologize that the last answer wrote in the wrong place a little ...
Indexes are a thing that cannot be combined. That is, if you have an index on the last_searched and featured fields, then this may not help.
One index on two fields is necessary.

Answer 2

habrahabr.ru/blogs/mysql/31129/

Answer 3

Reading the official documentation:
"The following are some cases where MySQL cannot use indexes to do an ORDER BY (note that MySQL will still use indexes to find the rows that match the WHERE clause):
- ORDER BY sorting is done on multiple keys: SELECT * FROM t1 ORDER BY key1,key2
- ORDER BY sorting is done when using inconsistent key parts: SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2
- ASC and DESC are mixed. SELECT * FROM t1 ORDER BY key_part1 DESC,key_part2 ASC
— Different keys are used to select rows and sort ORDER BY: SELECT * FROM t1 WHERE key2=constant ORDER BY key1
- Multiple tables are linked and the columns being sorted by ORDER BY are not just the first non-const table used to fetch rows (this is the first table in the EXPLAIN output that does not use the const, const, method of fetching rows) .
- There are various ORDER BY and GROUP BY expressions.
- The table index used is of a type that does not provide sorted storage of rows (like a HASH index in HEAP tables). „

Answer 4

Optimization is good, but why pull the list from this big table every time? Make a separate table, clearing it by crown / once a day. And it is better to store in apc / memcached / etc. Yes, at least make a cache on the files.

Answer 5

show explain and ddl tables with indexes