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 drops dramatically after adding the proper index to the table

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

Fixing image distortion on websites using Firefox/Iceweasel 3.5 on Debian testing with intel xorg driver

Lately I noticed some image distortion appearing on some websites using my laptop with Debian squeeze. Menus on swiftfox did not appear as they should, some logos appeared out of their place and there were artifacts and other annoying things. For example Planet Gnome looked like this:
image-distortion
When using iceweasel 3.0.12 everything looked fine. Then I followed a guide to install Iceweasel 3.5 from experimental to my system. Images looked distorted again. So there must have been a problem with the latest xulrunner….

After some googling I bumped into Debian bug #491871 – [965GM EXA] display corruption with xulrunner 1.9. Following post #67 on that thread I was able to repair my xorg.conf to something that fixed the image distortion. Now Planet Gnome looks like this:
no-image-distortion

Some info:

# apt-cache policy iceweasel xserver-xorg-video-intel xulrunner-1.9.1
iceweasel:
Installed: 3.5.1-1
Candidate: 3.5.1-1
Version table:
*** 3.5.1-1 0
1 http://ftp.debian.org experimental/main Packages
100 /var/lib/dpkg/status
3.0.12-1 0
500 http://ftp.de.debian.org squeeze/main Packages
99 http://ftp.de.debian.org sid/main Packages
xserver-xorg-video-intel:
Installed: 2:2.3.2-2+lenny6
Candidate: 2:2.3.2-2+lenny6
Version table:
2:2.8.0-2 0
99 http://ftp.de.debian.org sid/main Packages
*** 2:2.3.2-2+lenny6 0
500 http://ftp.de.debian.org squeeze/main Packages
100 /var/lib/dpkg/status
xulrunner-1.9.1:
Installed: 1.9.1.1-2
Candidate: 1.9.1.1-2
Version table:
*** 1.9.1.1-2 0
1 http://ftp.debian.org experimental/main Packages
100 /var/lib/dpkg/status