All tables have an o ptimal set o f indexes—which may be no indexes at all. Over time, indexes may o utlive their usefulness. Indexes will be added, remo ved, and altered as data is added to the database and query patterns change. A co mmo n mistake made by no vice pro grammers and database administrato rs who are eager to "o ptimize"
perfo rmance is to add indexes to satisfy every imaginable query. This o verzealo us indexing strategy is the wro ng appro ach fo r a few reaso ns:
To o many indexes will hurt database perfo rmance, especially fo r inserts, updates, and deletes. Query patterns change, so it is impo ssible to implement indexes to co ver every po ssible query.
Fo r small tables, database engines may igno re all indexes since it is o ften faster to grab all ro ws in the table than to wo rk with indexes.
Abo ve all, ho w do yo u kno w an index will help perfo rmance, witho ut any data to suppo rt yo ur so lutio n? Yo u wo uldn't replace the engine in yo ur car just because it started running slo wly!
Note
So me databases (such as SQL Server) have two o r mo re types o f indexes. Each type o f index has certain pro perties, and cho o sing the pro per index has a direct effect o n database perfo rmance. Generally speaking, MySQL o nly has o ne type o f index, and we do n't have to co ncern o urselves with that type o f detail.
If yo u are creating a new database, there are basic rules yo u sho uld fo llo w to get o ff to a go o d start. Fo llo w these steps whenever yo u start a new database pro ject.
Inde xing St e ps:
1. Always create primary keys fo r tables. If yo ur table do esn't seem to have a primary key, o r the primary key wo uld be all (o r nearly all) co lumns, co nsider adding a surrogate key. A surro gate key is a primary key that is generated by the database—in MySQL, an AUTO_INCREMENT co lumn.
2. Set up fo reign key co nstraints and index appro priately.
3. Create indexes fo r co lumns used in the WHERE clause fo r frequently used queries. Fo r example, if yo u frequently query the cust o m e r table using WHERE last _nam e ='some value', co nsider indexing the last _nam e co lumn.
To make the jo b o f the database administrato r easier, MySQL will let yo u see its query plan in o rder to figure o ut what is go ing o n "under the ho o d." The co mmand yo u use to view the query plan is the same co mmand yo u use to sho w the structure o f the table—EXPLAIN. Yo u can also use the syno nym, DESCRIBE.
Let's EXPLAIN a simple query. Lo g into MySQL as ro o t and co nnect to the sakila database and then perfo rm these steps.
Type the fo llo wing at the MySQL pro mpt: mysql> explain select * from rental;
+----+---+---+---+---+---+---+---+---+--- ----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ex tra |
+----+---+---+---+---+---+---+---+---+--- ----+
| 1 | SIMPLE | rental | ALL | NULL | NULL | NULL | NULL | 16298 | |
+----+---+---+---+---+---+---+---+---+--- ----+
1 row in set (0.00 sec) mysql>
What do these co lumns mean?
Co lum n De script io n
id The sequential number o f the se le ct in the query
select_type Type o f select statement—if it is a simple query, sub query, o r o ther table The name o f the table used in the query
type Jo in type—ho w this table is co mbined with o ther tables to fo rm the result po ssible_keys The indexes that might be used to satisfy the query
key The actual index that will be used in the query, o r NULL if no index was used key_len The length o f key that will be used
ref The co lumns used to jo in tables
ro ws The number o f ro ws examined fo r the query
Extra No tes o n ho w MySQL will pro cess the query, like if it needs to use a tempo rary table
Note
Fo r a full descriptio n o f EXPLAIN statement o ptio ns, see the MySQL site. Let's lo o k back at the results fro m the EXPLAIN statement.OBSERVE:
+----+---+---+---+---+---+---+---+---+--- ----+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ex tra |
+----+---+---+---+---+---+---+---+---+--- ----+
| 1 | SIMPLE | rental | ALL | NULL | NULL | NULL | NULL | 16298 | |
+----+---+---+---+---+---+---+---+---+--- ----+
We're selecting fro m the re nt al table, which has no po ssible _ke ys (indicat e d by NULL), so MySQL wo n't use any inde xe s, and the database needs to lo o k at appro ximately 16 29 8 ro ws. Yo ur ro w co unt might be slightly different —it is just an estimate. There are no o ther tables listed because we do n't have any jo ins. There is no info rmatio n abo ut a WHERE clause, because we didn't use a WHERE clause.
The result o f EXPLAIN do esn't sho w any po ssible _ke ys o n the rental table. This means MySQL didn't find any indexes o n re nt al that co uld be used to answer the query. This do esn't mean that re nt al do esn't have any indexes, ho wever. Ho w can we check to see what indexes exist o n rental? We'll use the sho w inde x statement.
Type the fo llo wing at the MySQL pro mpt: mysql> show index from rental;
+---+---+---+---+---+---+ ---+---+---+---+---+---+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---+---+---+---+---+---+ ---+---+---+---+---+---+
| rental | 0 | PRIMARY | 1 | rental_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 0 | rental_date | 1 | rental_date | A | 16298 | NULL | NULL | | BTREE | |
| rental | 0 | rental_date | 2 | inventory_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 0 | rental_date | 3 | customer_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 1 | idx_fk_inventory_id | 1 | inventory_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 1 | idx_fk_customer_id | 1 | customer_id | A | 1253 | NULL | NULL | | BTREE | |
| rental | 1 | idx_fk_staff_id | 1 | staff_id | A | 3 | NULL | NULL | | BTREE | |
+---+---+---+---+---+---+ ---+---+---+---+---+---+
7 rows in set (0.03 sec)
Let's take a clo ser lo o k: OBSERVE:
+---+---+---+---+---+---+ ---+---+---+---+---+---+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---+---+---+---+---+---+ ---+---+---+---+---+---+
| rental | 0 | PRIMARY | 1 | rental_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 0 | rental_date | 1 | rental_date | A | 16298 | NULL | NULL | | BTREE | |
| rental | 0 | rental_date | 2 | inventory_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 0 | rental_date | 3 | customer_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 1 | idx_fk_inventory_id | 1 | inventory_id | A | 16298 | NULL | NULL | | BTREE | |
| rental | 1 | idx_fk_customer_id | 1 | customer_id | A | 1253 | NULL | NULL | | BTREE | |
| rental | 1 | idx_fk_staff_id | 1 | staff_id | A | 3 | NULL | NULL | | BTREE | |
+---+---+---+---+---+---+ ---+---+---+---+---+---+
7 rows in set (0.03 sec) The keys in re nt al are:
the PRIMARY key o n re nt al_id
the co mpo site key o n re nt al_dat e o n re nt al_dat e, inve nt o ry_id, and cust o m e r_id the fo reign key idx_f k_inve nt o ry_id o n inve nt o ry_id
the fo reign key idx_f k_cust o m e r_id o n cust o m e r_id the fo reign key idx_fk_staff_id o n staff_id
Fo r a full descriptio n o f these fields, see the MySQL web site, but the mo st impo rtant fields are:
Co lum n De script io n
T able T he t able nam e
No n_unique If true, the co lumns in the index must be uniquely identify a ro w Ke y_nam e T he nam e o f t he inde x
Seq_in_index Fo r co mpo site indexes (indexes acro ss multiple co lumns)—the po sitio n o f the co lumn in theindex Co lum n_nam e T he nam e o f t he t able co lum n
Co llatio n The so rt o f the index (no t currently used)
Cardinalit y An e st im at e o f t he num be r o f unique ro ws in t he inde x
Sub_part The number o f indexed characters, if the co lumn is o nly partly indexed (perhaps first 5 characterso f last name are indexed) Packed Ho w the index is packed
Null If yes, the co lumn may co ntain NULL Index_type The index metho d (BTREE, FULLTEXT, etc) Co mment Index co mments