Adding a MySQL index to improve query speed

Quags

Administrator
Staff member
Many times I log into a server a client says is slow and see MySQL as the top CPU use. When this happens I always check MySQL for index's. Sometimes this can easily be found and I document one example. This example is done as root in SSH on a cpanel server, but it can be applied to any server running MySQL.

First, I got a ticket about a slow server and found, MySQL using most of the cpu. Top showed

Code:
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 4211 mysql     20   0  342m  71m 4768 S 24.9  3.8   1:07.26 mysqld


I then ran mysqladmin processlist and saw

Code:
| 513 | username | localhost | dbname | Query   | 4    | Sending data | SELECT * FROM `PLD_LINK` WHERE `OWNER_EMAIL` LIKE 'HIDDEN_EMAIL_ADDRESS' LIMIT 0 , 30 |
| 515 | username | localhost | dbname | Sleep   | 4

The next step was to check dbname to make sure OWNER_EMAIL had an index. To do so as root in ssh I ran mysql then use dbname;

Next I ran describe PLD_LINK; because the query was SELECT * FROM PLD_LINK


The OWNER_EMAIL section came back with

Code:
'| OWNER_EMAIL        | varchar(255) | YES  |     | NULL                |                             |
'
No index, if it did the key area would say MUL


So now you need to add the index


Code:
mysql> alter table PLD_LINK add index `OWNER_EMAIL` (`OWNER_EMAIL`);
Query OK, 43998 rows affected (4 min 56.09 sec)
Records: 43998  Duplicates: 0  Warnings: 0
When done the describe command came back with

Code:
| OWNER_EMAIL        | varchar(255) | YES  | MUL | NULL                |                             |
The MUL show's it had an index and the CPU usage has dropped.
 

Quags

Administrator
Staff member
OWNER_EMAIL is a row name in a table in my example. The actual table name varies based on your own install.
 
Top