EXPLAIN command to help figure that out. In doing so, we’ll see how adding an index or simply rephrasing a query can often better use an existing index and greatly improve performance.
mysql> describe individual
-> ;
+————+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+—————+——+—–+———+——-+
| INDID | int(11) | NO | PRI | NULL | |
| firstname | varchar(64) | NO | | NULL | |
| middlename | varchar(64) | YES | | NULL | |
| lastname | varchar(64) | NO | | NULL | |
| title | varchar(16) | YES | | NULL | |
| gender | enum(’M',’F') | NO | | NULL | |
| sis | varchar(64) | YES | | NULL | |
| natid | varchar(32) | YES | | NULL | |
| birthdate | date | YES | | NULL | |
| tel | varchar(32) | YES | | NULL | |
| fax | varchar(32) | YES | | NULL | |
| wtel | varchar(32) | YES | | NULL | |
| wfax | varchar(32) | YES | | NULL | |
| mobile | varchar(32) | YES | | NULL | |
| email | varchar(128) | YES | | NULL | |
| username | varchar(128) | YES | | NULL | |
| passwdhash | varchar(128) | YES | | NULL | |
| hashtype | enum(’MD5′) | YES | | NULL | |
+————+—————+——+—–+———+——-+
18 rows in set (0.00 sec)
The relationship is many-to-many because a single individual may be associated with multiple casus.
When you write a query against a primary key or unique index, MySQL should know that there can be only a single match for each value. Indeed, this query is very fast:
mysql> SELECT firstname, lastname FROM individual WHERE INDID =’3′;
+———–+———-+
| firstname | lastname |
+———–+———-+
| LOUISA | Engelen |
+———–+———-+
1 row in set (0.00 sec)
Just as it’s obvious to you or me, MySQL knows that only one record can possibly match. Its strategy for finding the row is straightforward: simply check the primary index for a match. If it exists, fetch the row. To verify that, let’s EXPLAIN it:
mysql> EXPLAIN SELECT firstname, lastname FROM individual WHERE INDID =’3′;
+—-+————-+————+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——-+—————+———+———+——-+——+——-+
| 1 | SIMPLE | individual | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+—-+————-+————+——-+—————+———+———+——-+——+——-+
1 row in set (0.00 sec)
What if we try fetching records based on a nonindexed column:
mysql> SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600;
+———-+
| COUNT(*) |
+———-+
| 3971 |
+———-+
1 row in set (1.04 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 302116
Extra: Using where
1 row in set (0.00 sec)
The NULL value in the key column of the EXPLAIN output tell us that MySQL won’t be using an index for this query. In fact, the NULL value in the possible_keys column tells us that there were no indexes to pick from at all. If this type of query is likely to be common, we can simply add an index and rerun the query (or the EXPLAIN) to verify that MySQL uses it.
mysql> ALTER TABLE Headline ADD INDEX (ExpireTime);
Query OK, 302116 rows affected (40.02 sec)
Records: 302116 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600;
+———-+
| COUNT(*) |
+———-+
| 3971 |
+———-+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM Headline WHERE ExpireTime >= 1112201600 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Headline
type: range
possible_keys: ExpireTime
key: ExpireTime
key_len: 4
ref: NULL
rows: 12009
Extra: Using where; Using index
1 row in set (0.00 sec)
The query now runs in 0.01 seconds instead of 1.04. The EXPLAIN output looks much better, with the new ExpireTime index being used for a range search. Note again the discrepancy between rows (12009) and the actual row count (3971). In a case like this, it might be possible to improve the estimate that MySQL makes by running either ANALYZE TABLE or OPTIMIZE TABLE on the Headline table.
Also, notice that MySQL said Using index. That means this is an index-only query. MySQL is able to get all the data it needs from the ExpireTime index, so it doesn’t bother fetching any of the rows from disk.
