Index for god sake!

[Chris]

This is an old post I put on the old mysqlhow2.com that ended up on modphp.org. Both sites are run by people I know so it's coo. Anyway, I thought it should be here as well.

Lets look at the results of the query without returning all the results:

NOTE: Use EXPLAIN
- Explain will show the possible rows to be examined in your query. (The more rows the slower the return)
- Example of EXPLAIN: (we will be using the world database by mysql.com)

EXPLAIN SELECT * from City where CountryCode =’USA’ and Population > ‘1000′ order by District;
Results:
+—-+————-+——-+——+—————+——+———+——+——+—————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+—————————–+
| 1 | SIMPLE | City | ALL | NULL | NULL | NULL | NULL | 4079 | Using where; Using filesort |
+—-+————-+——-+——+—————+——+———+——+——+—————————–+
1 row in set (0.00 sec)
As you can see it did a full table scan(slow)

We have 2 options:

1st Option add Indexes

mysql> Alter table City add index(CountryCode);
Query OK, 4079 rows affected (0.17 sec)
Records: 4079 Duplicates: 0 Warnings: 0

Now the new results

mysql> EXPLAIN SELECT * from City where CountryCode =’USA’ and Population > ‘1000′ order by District;
Results:
+—-+————-+——-+——+—————+————-+———+——-+——+—————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+————-+———+——-+——+—————————–+
| 1 | SIMPLE | City | ref | CountryCode | CountryCode | 9 | const | 267 | Using where; Using filesort |
+—-+————-+——-+——+—————+————-+———+——-+——+—————————–+
1 row in set (0.00 sec)

As you can see the results are much better with only 273 rows returned.

2nd option is to optimize your query by giving more details in the search. And add indexes as needed.

EXPLAIN SELECT * from City where CountryCode =’USA’ and Population > ‘1000′ and District = ‘California’;
Results:
+—-+————-+——-+——+—————+————-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+————-+———+——-+——+————-+
| 1 | SIMPLE | City | ref | CountryCode | CountryCode | 9 | const | 267 | Using where |
+—-+————-+——-+——+—————+————-+———+——-+——+————-+
1 row in set (0.00 sec)

Now on this query make a different alter

mysql> Alter table City add index(District);
mysql> EXPLAIN SELECT * from City where CountryCode =’USA’ and Population > ‘1000′ and District = ‘California’;
Results:
+—-+————-+——-+————-+———————-+———————-+———+——+——+—————————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+———————-+———————-+———+——+——+—————————————————-+
| 1 | SIMPLE | City | index_merge | CountryCode,District | District,CountryCode | 60,9 | NULL | 3 | Using intersect(District,CountryCode); Using where |
+—-+————-+——-+————-+———————-+———————-+———+——+——+—————————————————-+
1 row in set (0.00 sec)

This result is much better from 4079 results to 59.

If you are having difficulty optimizing your database and queries please visit
Query Speed

Want to setup your own world database? Go here:
World Setup