|
I'd like to address three common misunderstandings regarding MySQL indexing, which can have an enormous impact on the performance of your queries.
I'm using this example in this article:
CREATE TABLE test (
id BIGINT UNSIGNED NOT NULL auto_increment,
col1 BIGINT UNSIGNED,
col2 BIGINT UNSIGNED,
PRIMARY KEY (id),
INDEX combined (col1, col2),
INDEX single (col1)
);
INSERT INTO test (col1, col2) VALUES (1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3,3);
Warning: This example uses the index single for clarification, although it's not good practice and generally speaking a very bad idea to use it. Will explain later.
Combined Indices
I've heard a lot of people saying that combined indices will help a lot to improve performance. While that is true, when you search on all the columns of a combined index, it is not necessarily true, when you only search on a subset of its columns.
So in the aforementioned example we've got a table with
- a primary key on an auto-increment column
- two numeric columns
- a combined index on both columns (col1, col2)
- an index on col1 only
I filled it with some data, so you can actually try it on your own by just using copy & paste.
Now the common misunderstanding is that you can query on any of the columns, and MySQL would use the combined index. Others say, you have to search on all of the index's columns. Both wrong!
 | With a combined index on col1 and col2, you can query on col1, or both columns, but not on col2 only. |
Well, you can query of course, but the index wouldn't be used:
EXPLAIN SELECT * FROM test WHERE col1 = 1;
+----+-------------+-------+------+-----------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | combined,single | combined | 9 | const | 3 | Using where |
+----+-------------+-------+------+-----------------+----------+---------+-------+------+-------------+
EXPLAIN SELECT * FROM test WHERE col1 = 1 AND col2 = 3;
+----+-------------+-------+------+-----------------+----------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+----------+---------+-------------+------+-------------+
| 1 | SIMPLE | test | ref | combined,single | combined | 18 | const,const | 1 | Using where |
+----+-------------+-------+------+-----------------+----------+---------+-------------+------+-------------+
EXPLAIN SELECT * FROM test WHERE col2 = 2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
See the last one, where we search on col2? possible_keys: NULL!
So, if you intend to search on col2 only, you need an explicit index on that column.
By the way, it does not matter in which order you use the columns of your combined index in your query.
Searching for Ranges
Another common mistake is to assume that MySQL uses indices when you search for a range of values, such as not equal, greater, or lesser than. Well, MySQL itself says something different!
Let's stick to the above example and try it:
EXPLAIN SELECT * FROM test WHERE col1 > 0;
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | combined,single | NULL | NULL | NULL | 9 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
Let's look at this result in detail:
- possible keys combined and single means: in theory both indices could be used, if you searched for a single value
- but as you didn't, you end up with key = NULL, hence no key is effectively being used
| When you search for ranges, such as >, <, !=, MySQL cannot use your indices |
We could play the same game, having a fixed value on col1 and a range on col2:
EXPLAIN SELECT * FROM test WHERE col1 = 1 and col2 > 1;
+----+-------------+-------+-------+-----------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | test | range | combined,single | combined | 18 | NULL | 3 | Using where |
+----+-------------+-------+-------+-----------------+----------+---------+------+------+-------------+
Here you can see that combined is being used for the first part of the where clause (col1 = 1), resulting in three remaining rows, which are not narrowed down further using an index.
An interesting exception occurs when your range includes only a single possible result:
EXPLAIN SELECT * FROM test WHERE col1 > 2;
+----+-------------+-------+-------+-----------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | test | range | combined,single | combined | 9 | NULL | 4 | Using where |
+----+-------------+-------+-------+-----------------+----------+---------+------+------+-------------+
As our maximum value for col1 was 3 (see above), >2 only matches a single result, turning the query effectively in: SELECT * FROM test WHERE col1 = 3. But that's a special case, and if you know this anyway, you wouldn't query for a range here 
Duplicate Indices
In the example table, I am using the indices single (col1) and combined (col1, col2). This is a duplicate, redundant, entirely useless thing. However, you'll see that very often.
Here's why you don't need that kind of duplicate index:
EXPLAIN SELECT * FROM test WHERE col1 = 2;
+----+-------------+-------+------+-----------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | combined,single | combined | 9 | const | 2 | Using where |
+----+-------------+-------+------+-----------------+----------+---------+-------+------+-------------+
You would expect that MySQL uses single, wouldn't you? Well it could do, but the thing is that it doesn't matter. col1 is the first column of combined, so it can use this index as well.
Let's force MySQL to use our single index (by dropping combined altogether) and see if that makes any difference:
ALTER TABLE test DROP INDEX combined;
EXPLAIN SELECT * FROM test WHERE col1 = 2;
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | test | ref | single | single | 9 | const | 2 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
So, you can see that it wouldn't make any difference whatsoever, if you had got an index on col1 only.
| There's no point having an extra index on a single column, which is the first column in a combined index anyway. The same applies for primary keys. |
Conclusion
Choose your indices carefully! The main questions should always be: On which columns do I really want to search in order to narrow down my results? Do I search for fixed, known values?
Keep in mind: Although having too many indices doesn't seem harmful, in fact it kills your writing performance.
|
|
Shortcuts
|