Using nested SELECTs or LEFT JOINs on big tables, can result in slow
performance in database queries. Suppose we have two tables:
tbl1 with 1500 records with id1 as primary key and tbl2 with 100,000 records with
id2 as primary key and fid
which the id1 in tbl1 is
a foreign key for it. The following diagram shows the situation:
If you want to run the following
queries it takes long to answer (on my 3.0GHz P4 machine with MySQL 4.1, it takes
more than 9 minutes to answer!):
WHERE id1 IN (SELECT fid
LEFT JOIN tbl2
ON tbl1.id1 = tbl2.fid
The cause of this problem is that MySQL has to start with the
first record and then read through the whole table to find the relevant rows.
The larger the table, the more this costs. To solve this problem and get a better performance, it's necessary to define
an INDEX on the fid in the second table. You can do
this by the following sql command:
ALTER TABLE `tbl2`
ADD INDEX `INX_FID` (`fid`)
are used to find rows with specific column values fast, without defining an
index, in both above queries, MySQL has to
scan all records in tbl2 so it takes long time to answer these queries.
In general, indexes are used as described in the following
- To quickly find the rows that match a WHERE clause.
- To retrieve rows from other tables when performing joins.
- To find the MIN() or MAX() value for a specific indexed column.
- To sort or group a table, if the sorting or grouping is
done on a leftmost prefix of a usable key (for example, ORDER BY key_part1,
key_part2). If all key parts are followed by DESC, the key is read in
The above scenario exists for all databases not only for
MySQL merely, so in general, indexes should be considered on all columns that are frequently
queried or accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
Without an index, each of these operations will require a table scan of your
table, potentially hurting performance.
To learn more about indexes visit the following pages: