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):
- Always:
user_id
int with=
[> 1 m users, but single users may have > 200K entries] - Sometimes:
status
varchar(20) withIN()
[7 possibilities] - Sometimes:
expiration_timestamp
datetime with<
[can be any timestamp] - Sometimes:
type
varchar(20)( withIN()
[7 possibilities] - Rare:
name
varchar(255) withLIKE
[with trailing wildcard, rarely repetitive] - Very rare:
tags
varchar(2000) withLIKE
[with leading and trailing wildcard] - Often:
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. status
and 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!
user_id
to locate any subset of that small size should be quick. As Barry mentioned, use theEXPLAIN ANALYZE
to understand all of your bottlenecks so you can decide how to appropriately tune your indexes / queries / table design. It's possible multiple smaller indexes rather than a single large composite index, as you mentioned in your post, would be best.