Let’s assume blabla table has millions of rows, look this query;

SELECT * FROM blabla WHERE ends_at <= 1506668369 and is_processed = 0

 

If you have a 2 column index AS ABOVE,  ‘is_processed‘ at first place and ‘ends_at‘ at second place in the index sequence, then it takes 0.0004 seconds in my server to retrieve rows.

If you have a 2 column index AS ABOVE,  ‘ends_at‘ at first place and ‘is_processed‘ at second place in the index sequence, then it takes 1.0733 seconds in my server to retrieve rows.

That’s a huge difference! Think your tables as a book always and your indexes as the table of contents page when you create your indexes!

SELECT * FROM blabla WHERE ends_at <= 1506668369 and is_processed = 0

In this query, you must clearly go to is_processed=0 pages first, in those pages then you can find ends_at <= 1506668369 rows. That way will be more easy to find those rows. That’s the way of MySQL indexes works.


On the other hand, there is always a left-to-right rule in MySQL indexes.

For example; if you have a triple column index with A,B,C sequence,

Those where clauses will benefit from your index;

where A , B , C

where A, B

where B

However, those where clauses will not use your index;

where B,C

where A, C

where B

where C