Assuming that I have a MySQL table with ~ 30 million entries and 40 columns I have an highly active query (5 queries/second) which is quite slow (avg. ~ 20 seconds) and has a high number of rows scanned (avg. 50.000 rows). Performance is getting worse and worse with the table growing. I want to solve the problem by adding the correct composite or even covering index.
The doctrine query is built by a dynamic query builder and involves the following properties (only userId is used in any query, all other columns are only used for filtering sometimes):
=[> 1 m users, but single users may have > 200K entries]
<[can be any timestamp]
LIKE[with trailing wildcard, rarely repetitive]
- Very rare:
LIKE[with leading and trailing wildcard]
orderBy id int DESC[id is the primary key, the orderBy is necessary]
Without having tested it (will require a production deployment with maintenance window including short downtime) I would propose the following solution:
CREATE INDEX listing ON items(user_id,status,type,name,expiration_timestamp,id);
Here is my reasoning: First of all, the
user_id is always used with an equality comparison, so this should be first.
type have an
IN clause, therefore they should be second. The third one is
name, because even if
LIKE with trailing wildcard is used it is highly selective. Indexing the
expiration_timestamp will help to significantly reduce the number of results. As MySQL uses indices for ordering it makes sense to put the
id at the end of the composite index. There is no reason to put tags in the index, because an index on a LIKE with a leading wildcard is useless.
Is this the correct approach or would you recommend to improve something here?
One fact I am not sure about furthermore: In case the query e.g. is without type or status, will MySQL be "intelligent" enough to use my composite index anyway? Still quite new to MySQL indexing, thank you for your help!