Multiple Column Index vs Multiple Indexes

Question

I have the following table in a MySQL database:

CREATE TABLE `secondary_images` (
  `imgId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `primaryId` int(10) unsigned DEFAULT NULL,
  `view` varchar(255) DEFAULT NULL,
  `imgURL` varchar(255) DEFAULT NULL,
  `imgDate` datetime DEFAULT NULL,
  PRIMARY KEY (`imgId`),
  KEY `primaryId` (`primaryId`),
  KEY `imgDate` (`imgDate`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

The SQL will be the following:

SELECT imgURL, view FROM secondary_images 
WHERE primaryId={$imgId} ORDER BY imgDate DESC

As you can see I made both the primaryId and imgDate, Index Keys. My thinking behind that was because the WHERE clause queries results using the primaryId, and the ORDER clause uses imgDate.

My question is, would it be better to use Multiple Indexes as I am right now? Or should I a Multiple Column Index (something I don't understand all too well at the moment)?

This is what I get from EXPLAIN:

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

NOTE: This is not using a Multiple Column Index, it is the result from using the above table description.

Answer 1

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.

Answer 2

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.