03/08/2011
please use indexes in your database tables
I don’t know how many times I’ve heard a sysadmin swearing at a web/application developer that has “forgotten” to add proper index to their database tables. Please dear developers, add those indexes, it’s not that hard!
Today I had to do it on a table for a xoops module. A single query could keep mysql very very busy…how busy ? the query could take more than 2 minutes, unless it was already cached! I’ll post the way I solved it as an example. It is actually usually quite easy to solve these kind of issues.
1) How to spot the problem
Your application is slow and your server’s “top” shows mysql running constantly at full load.
2) Find the problematic query. Login to mysql and run a ‘show processlist;’
mysql> show processlist; +------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ | 1792 | admin | localhost | aaa | Sleep | 0 | | NULL | | 4790 | user | localhost | bbb | Query | 0 | Sorting result | SELECT * FROM foobar WHERE (com_rootid = '22797' AND com_id >= '22797') ORDER BY com_id | | 4791 | user | localhost | bbb | Query | 0 | Sorting result | SELECT * FROM foobar WHERE (com_rootid = '22797' AND com_id >= '22797') ORDER BY com_id | | 4817 | admin | localhost | NULL | Query | 0 | NULL | show processlist | +------+--------------+-----------+--------------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec)
3) Take a closer look at the structure of the table
mysql> describe foobar; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | com_id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment | | com_pid | mediumint(8) unsigned | NO | MUL | 0 | | | com_rootid | mediumint(8) unsigned | NO | | 0 | | | com_modid | smallint(5) unsigned | NO | | 0 | | | com_itemid | mediumint(8) unsigned | NO | MUL | 0 | | | com_icon | varchar(25) | NO | | | | | com_created | int(10) unsigned | NO | | 0 | | | com_modified | int(10) unsigned | NO | | 0 | | | com_uid | mediumint(8) unsigned | NO | MUL | 0 | | | com_ip | varchar(15) | NO | | | | | com_title | varchar(255) | NO | MUL | | | | com_text | text | NO | | NULL | | | com_sig | tinyint(1) unsigned | NO | | 0 | | | com_status | tinyint(1) unsigned | NO | | 0 | | | com_exparams | varchar(255) | NO | | | | | dohtml | tinyint(1) unsigned | NO | | 0 | | | dosmiley | tinyint(1) unsigned | NO | | 0 | | | doxcode | tinyint(1) unsigned | NO | | 0 | | | doimage | tinyint(1) unsigned | NO | | 0 | | | dobr | tinyint(1) unsigned | NO | | 0 | | +--------------+-----------------------+------+-----+---------+----------------+ 20 rows in set (0.01 sec)
4) run EXPLAIN on the problematic query
mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id >= '17536') ORDER BY com_id; +----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+ | 1 | SIMPLE | foobar | ALL | PRIMARY | NULL | NULL | NULL | 18271 | Using where; Using filesort | +----+-------------+--------------------+------+-----------------------+------+---------+------+-------+-----------------------------+ 1 row in set (0.02 sec)
If “type” is “ALL”, like the case above, then you have a problem. This is the worst “type” you could have. The query above scans the whole table and has to go through 18271 rows and then has to do a filesort on these rows.
5) Look at indexes, if there are any….
mysql> show index from foobar; +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | foobar | 0 | PRIMARY | 1 | com_id | A | 18272 | NULL | NULL | | BTREE | | | foobar | 1 | com_pid | 1 | com_pid | A | 1 | NULL | NULL | | BTREE | | | foobar | 1 | com_itemid | 1 | com_itemid | A | 18 | NULL | NULL | | BTREE | | | foobar | 1 | com_uid | 1 | com_uid | A | 6 | NULL | NULL | | BTREE | | | foobar | 1 | com_title | 1 | com_title | A | 18272 | 40 | NULL | | BTREE | | +--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 5 rows in set (0.00 sec)
6)Create necessary new index
ALTER TABLE `DB`.`foobar` ADD INDEX `com_id_rootid` ( `com_id` , `com_rootid` )
7) Explain the query again
mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id >= '17536') ORDER BY com_id; +----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | foobar | ref | PRIMARY,com_id_rootid | com_id_rootid | 3 | const | 1 | Using where; Using filesort | +----+-------------+--------------------+------+------------------------+---------------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec)
now the query is of type “ref” and it only fetches 1 row!
If the query could be optimized even more by the developer and he/she could replace ‘>=’ with ‘=’ then it would make us even happier
mysql> explain SELECT * FROM foobar WHERE (com_rootid = '17536' AND com_id = '17536') ORDER BY com_id; +----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | foobar | const | PRIMARY,com_id_rootid | PRIMARY | 3 | const | 1 | | +----+-------------+--------------------+-------+-----------------------+---------+---------+-------+------+-------+ 1 row in set (0.00 sec)
the type would now be ‘const’, which is the best type we could have.
The problem got fixed with just one command! Now the queries take milliseconds to finish and the web application is as fast as ever. The server load dropped from 2.5 to 0.4 with just this tiny addition to the indexes.
CPU Load
So dear developers, please run EXPLAIN to your queries before you submit your applications and if you find queries with type=ALL try to add indexes! It saves all of us a loooooot of trouble!
For anyone who wants to read a nice blog post about EXPLAIN and what each part of the output means, I recommend that he/she reads this: MySQL Explain – Reference
Filed by kargig at 22:06 under Linux
Tags: explain, index, Linux, mysql, problem, query, slow, web
3 Comments | 8,479 views
