Taken from:
http://mysqlhow2.com/viewtopic.php?t=8
If you have a database that is returning slow result you might want to optimize your queries and do indexing.
But how to tell which of the queries need to be optimized.
Lets look at the rulsts of the querey without returning all the results:
You will use the EXPLAIN clause
Explain clause will show rows returned 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
http://dev.mysql.com/doc/refman/4.1/en/query-speed.html
Want to setup your own world database? Go here:
http://dev.mysql.com/doc/world-setup/en/world-setup.html
ChrisS
Comments are closed.