{{parent page="MysqlTuning"}}

===SQL tuning on MySQL===

==Create the test database and populate it with data.==

%%
create database tunning1;
use tunning1;
create table name(id int,fname varchar(20), lname varchar(20), gender int);
insert into name values (1,'Peter','Parker','m');
insert into name values (2,'Samantha','Pierce','f');
insert into name values (3,'Jennifer','Wong','f');
insert into name values (4,'Philip','Seamor','m');
%%

==Create single column index==
%%
mysql> explain select * from name where fname = 'Peter';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | name | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> create index ix_fname on name(fname);
mysql> explain select * from name where fname = 'Peter';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | name | ref | ix_fname | ix_fname | 23 | const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
%%

==Multiple single column index==
%%
mysql> explain select * from name where fname = 'Peter' and lname = 'Parker';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | name | ref | ix_fname | ix_fname | 23 | const | 1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> create index ix_lname on name(lname);
mysql> explain select * from name where fname = 'Peter' and lname = 'Parker';
+----+-------------+-------+------+-------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | name | ref | ix_fname,ix_lname | ix_fname | 23 | const | 1 | Using where |
+----+-------------+-------+------+-------------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)
%%


==Multiple column index==
Example below reduce the need to build a temp table for sorting the resultset.

%%
mysql> explain select * from name where fname = 'Peter' group by lname;
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
| 1 | SIMPLE | name | ref | ix_fname | ix_fname | 23 | const | 1 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)
%%

At this point, MySQL has an index for fname and lname, but they're not related in anyway. So **ix_fname** gives mysql a resultset where **lname** is unsorted. So MySQL seeks out to create a temp table for the sorting. This can be prevented:
%%
mysql> create index ix_fname_lname on name(fname,lname);
mysql> explain select * from name where fname = 'Peter' group by lname;
+----+-------------+-------+------+-------------------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------+----------------+---------+-------+------+-------------+
| 1 | SIMPLE | name | ref | ix_fname,ix_fname_lname | ix_fname_lname | 23 | const | 1 | Using where |
+----+-------------+-------+------+-------------------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
%%

At this point, the indexes **ix_fname** and **ix_lname** is already covered by **ix_fname_lname** and hence should be dropped. You can run explain again on all queries in this example and they're all covered by index.
%%
mysql> drop index ix_fname on name;
mysql> drop index ix_lname on name;
%%

==Foreign key index==
Now let's create a contact table which is related to **name**
%%
create table contact (contact_id int, name_id int, phone1 varchar(20), phone2 varchar(20), email varchar(30));
insert into contact values (1,1,'123-4567','234-5678','ken@fong.com');
insert into contact values (2,2,'123-4567','234-5678','user2@domain.tld');
insert into contact values (3,3,'123-4567','234-5678','user3@domain.tld');
insert into contact values (4,4,'123-4567','234-5678','user4@domain.tld');
insert into contact values (5,1,'123-45671','234-5678','ken@fong.com');
insert into contact values (6,2,'123-45671','234-5678','user2@domain.tld');
insert into contact values (7,3,'123-45671','234-5678','user3@domain.tld');
insert into contact values (8,4,'123-45671','234-5678','user4@domain.tld');
%%

If we do a JOIN query now, a full table scan on **contact** is required:
%%
mysql> explain select n.fname, n.lname, c.phone1 from name n, contact c where n.id = c.name_id and n.fname = 'Ken';
+----+-------------+-------+------+------------------------+----------------+---------+-------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+----------------+---------+-------+------+--------------------------------+
| 1 | SIMPLE | n | ref | PRIMARY,ix_fname_lname | ix_fname_lname | 23 | const | 1 | Using where |
| 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 8 | Using where; Using join buffer |
+----+-------------+-------+------+------------------------+----------------+---------+-------+------+--------------------------------+
2 rows in set (0.00 sec)
%%

So we create a foreign key index on **contact** which reference **name.id** so you will have this relation established:
%%
name contact
======= ===============
id (pk)----| contact_id (pk)
fname |--->name_id(fk)
lname phone1
gender phone2
email


alter table contact add foreign key fk_name_id (name_id) references name(id);
mysql> alter table contact add foreign key (name_id) references name(id);
%%

Now we do the same query (notice the queries below are the same) again and mysql reads only the rows that are necessary (hence remove the expesive full table scan):
%%
mysql> explain select n.fname, n.lname, c.phone1 from name n, contact c where n.id = c.name_id and n.fname = 'Ken';
+----+-------------+-------+------+------------------------+----------------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+----------------+---------+---------------+------+-------------+
| 1 | SIMPLE | n | ref | PRIMARY,ix_fname_lname | ix_fname_lname | 23 | const | 1 | Using where |
| 1 | SIMPLE | c | ref | fk_name_id | fk_name_id | 5 | tunning1.n.id | 2 | Using where |
+----+-------------+-------+------+------------------------+----------------+---------+---------------+------+-------------+
2 rows in set (0.00 sec)

mysql> explain select n.fname, n.lname, c.phone1 from name n inner join contact c on (n.id = c.name_id) where fname = 'Ken';
+----+-------------+-------+------+------------------------+----------------+---------+---------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------+----------------+---------+---------------+------+-------------+
| 1 | SIMPLE | n | ref | PRIMARY,ix_fname_lname | ix_fname_lname | 23 | const | 1 | Using where |
| 1 | SIMPLE | c | ref | fk_name_id | fk_name_id | 5 | tunning1.n.id | 2 | Using where |
+----+-------------+-------+------+------------------------+----------------+---------+---------------+------+-------------+
2 rows in set (0.00 sec)
%%