Multiple Column Index vs Multiple Indexes

You should use a multi-column index on (primaryId, imgDate) so that MySQL is able to use it for selecting the rows and sorting.

If all the columns used for sorting are not in the index used for selection, MySQL uses the "filesort" strategy, which consists of sorting all rows (in memory if there is not too much rows; on disk else).

If all columns used for sorting are in the index, MySQL uses the index to get the rows order (with some restrictions).

MySQL uses a tree structure for the indexes. This allows to access keys in order directly without sorting.

A multi-column index is basically an index of the concatenation of the columns. This allows MySQL to find the first row matching primaryId={$imgId}, and then access all the other rows directly in the right order.

With a single-row index on primaryId, MySQL can find all the rows matching primaryId={$imgId}, but it will find the rows in no particular order; so it will have to sort them after that.

See EXPLAIN and ORDER BY Optimization.

Your explain looks like this:

[id] => 1 [select_type] => SIMPLE [table] => secondary_images [type] => ref [possible_keys] => primaryId [key] => primaryId [key_len] => 5 [ref] => const [rows] => 1 [Extra] => Using where; Using filesort 

Let's walk through it.

[id] => 1 

Means we're talking about the first table. You're only calling one table in your statement.

[select_type] => SIMPLE 

We're doing a simple SELECT.

[table] => secondary_images 

The table name in question.

[type] => ref 

The select type, most important for joins.

[possible_keys] => primaryId 

This is an important field: it shows which keys can possibly be used to aid the query in executing faster. In this case, only your primary key is deemed useful.

[key] => primaryId 

This is an important field: it shows which key(s) finally were used. In this case, the primary key.

[key_len] => 5 [ref] => const [rows] => 1 

Guesssing the number of rows examined by the query.

[Extra] => Using where; Using filesort 

The most important field imho. - Using where: You are using a where-statement. Quite ok. - Using filesort: the result of your query is so big, it can't be sorted in memory. MySQL has to write it to a file, sort the file, and then output. This means disk access and will slow down everything. Adding an index that can aid the sorting often helps, but solving "using filesort" is a chapter on its own.