Saturday, March 11, 2006

optimizing mysql tables

By Justin Silverton

Many times, slow access to a mysql database can be the result of Badly defined or non-existent indexes and fixing these can often lead to better performance. Here is an

example table:

CREATE TABLE address_book (

contact_number char(10) NOT NULL,
firstname varchar(40),
surname varchar(40),
address text,
telephone varchar(25)

example query: SELECT firstname FROM address_book WHERE contact_number = '12312';

This will retrieve the firstname of a person added to the address_book table, based on the contact number.

Without any kind of indexes added to this table, mysql will have to search through each row to find the item that you would like to find, which is very inefficient.

Optimizing your table

There is a built-in command called explain, that can show you what, if any, indexes that are being used to retrieve results.


EXPLAIN SELECT firstname FROM address_book WHERE contact_number = '12312';

This will return a set of results that will tell you how myql is processing the results

table: The table the output is about (will show multiple if you have joins)
type: The type of join is being used.best to worst the types are: system, const, eq_ref, ref, range, index, all
possible_keys: Shows which possible indexes apply to this table
key: And which one is actually used
key_len: The length of the key used. The shorter that better.
ref: The column, or a constant, is used
rows: The number of rows mysql believes it must examine to get the data
extra: You don't want to see "using temporary" or "using filesort"

and index can be added to the above example table using the following command:

ALTER TABLE address_book ADD INDEX(contact_number);

you can also add an index on only part of a varchar. In the following, I will add an index on only 8 of the 10 characters.

ALTER TABLE address_book ADD INDEX(contact_number(8));

Why would you want to do this?

Indexes do increase performance in the right situations, but they are also a tradeoff between speed and space. The bigger an index is, the more space it will consume on your harddrive.

Using the query optimizer/analyzer

the following command can analyze your table key distribution to find out the best indexes to use:

analyze table *tablename*

also, another thing to keep in mind is the fact that over time, update and delete operations leave gaps in the table, which will cause un-needed overhead when reading data from your tables.

from time to time, it is a good idea to run the following (which will fix the above issue):

optimize table *tablename*


Post a Comment

Links to this post:

Create a Link

<< Home