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 namecreate 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)
There are no comments on this page. [Add comment]