What indexes to make for such a query in mysql?

Question

MySQL

explain select * from class_actions where type like 'newad' or type like 'renewad' and object_id=335046;
There is already an index on the type field and double on the type + object_id
explain fields shows that the indexes do not work.

Answer 1

where type in ('newad','renewad') and object_id=335046
Here at such request the index on object_id & type will be used.

Answer 2

It is rather obvious that it is necessary to hang up an index on object_id.
If your type is text (otherwise why like), then it definitely makes sense to change it to enum, then the composite (type + object_id) can start working (only the query will need to be rewritten from like to in ('newad','renewad')

Answer 3

The sequence of columns in the index is exactly (1) type, (2) object_id?
For the sake of testing, I put two indexes on type (VARCHAR), object_id (int):
type + object_id and object_id + type - explain shows that the first one works.

Answer 4

like 'newad' or type like 'renewad'
sorry, but there are no wildcards here, if it's a full match, wouldn't it be better to use =?

Answer 5

When using OR, indexes do not work!