MySQL index on a field to sort?
I have a table like this:
id, entity_id, total_likes, total_comments, total_shares, created_at
In fact, there are more fields, simplified for example.
The request to her looks like this:
SELECT * FROM table WHERE entity_id = ? AND created_at > ? ORDER BY total_comments
For such a query, I created an index entity_id_created_at_total_comments . But what if I need to sort by total_shares or total_likes ? I can't add these fields to the existing index, they won't work. Do I need to create 2 more indexes entity_id_created_at_total_shares and entity_id_created_at_total_likes ?MySQL Kira Khan, Jun 22, 2019
create different ones and check which one is better through explain.
I wouldn't put order in the index at all.
If there is a lot of data, then a separate index on the field that will be in order by.
Or just for where indices and enough.
In general, it is easier to check how fast it will work.
Create on all combinations will definitely slow down the selection and bloat the table.Gavin Contreras
To place the code, please use CodePen or similar tool. Thanks you!