Here is a simple demonstration of the importance of keys in query performance. The example uses three tables from the Sakila database (downloadable from http://dev.mysql.com/doc/sakila/en/sakila.html)--customer with a primary key and about 700 rows, and rental and payment, each with a primary key, a foreign key referencing customer, and about 16,000 rows. The query just counts joined customer, rental and payment rows:
SELECT COUNT(*)
FROM customer c
JOIN rental r USING (customer_id)
JOIN payment p USING (customer_id);
First, turn off caching so we can see the effects of indexes by themselves:
set query_cache_type=0;
set query_cache_size=0;
reset query cache;
The query with a SQL_NO_CACHE qualifier finishes in three-quarters of a second:
SELECT SQL_NO_CACHE COUNT(*) AS JoinResultRowsNoKeys
FROM customer c
JOIN rental r USING (customer_id)
JOIN payment p USING (customer_id);
+-----------------------+
| JoinResultRowsAllKeys |
+-----------------------+
| 445505 |
+-----------------------+
1 row in set (0.47 sec)
Here is EXPLAIN output for the query:
+----+-------------+-------+-------+--------------------+--------------------+---------+----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+--------------------+---------+----------------------+------+-------------+
| 1 | SIMPLE | c | index | PRIMARY | idx_fk_store_id | 1 | NULL | 541 | Using index |
| 1 | SIMPLE | r | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.c.customer_id | 13 | Using index |
| 1 | SIMPLE | p | ref | idx_fk_customer_id | idx_fk_customer_id | 2 | sakila.c.customer_id | 14 | Using index |
+----+-------------+-------+-------+--------------------+--------------------+---------+----------------------+------+-------------+
Multiplying the rowcounts for each table EXPLAIN lists gives a rough measure of how many rows the query engine will have to examine: 541*13*14, or 98,462.
Now make copies of those tables in another database, with no indexes at all, and run the same query on the new raw tables:
drop schema if exists saktest;
create schema saktest;
use saktest;
drop table if exists customer,rental,payment;
create table customer engine=innodb select * from sakila.customer;
create table rental engine=innodb select * from sakila.rental;
create table payment engine=innodb select * from sakila.payment;
SELECT SQL_NO_CACHE COUNT(*) AS JoinResultRowsNoKeys
FROM customer c
JOIN rental r USING (customer_id)
JOIN payment p USING (customer_id);
+----------------------+
| JoinResultRowsNoKeys |
+----------------------+
| 445505 |
+----------------------+
1 row in set (23.34 sec)
It's almost 50 times slower. EXPLAIN shows why:
+----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+
| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 637 | |
| 1 | SIMPLE | r | ALL | NULL | NULL | NULL | NULL | 16130 | Using where; Using join buffer |
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 16272 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+-------+--------------------------------+
Without indexes, it must examine 637*16130*16272=167,191,708,320 rows, more than a million-and-a-half times more row comparisons than the same query with indexes. We could take the fact that it's only 50 times slower as a tribute to the query engine.
If your query is sluggish, look at the keys.
|
|