I use indexes like most developpers do (mostly on... well ! index), but i'm sure there is a lot of subtle way to optimize a database using index. I'm not sure if it is specific to any implementation of a DBMS.

My question is : what are good examples of how to use index (except for basic, obvious cases), and how does a DBMS optimize its database when you specify an index on a table ?

  • In further thinking of this question, this question is too general for this site. If we change the scope of the question that might be appropriate, otherwise this question is not appropriate for the site. – jcolebrand Jan 7 '11 at 1:34
  • I like to explain indexes using the library metaphor mysqlperformanceblog.com/2011/08/30/… See if that helps.. – Jonathan Sep 14 '11 at 17:54

Think of an index as "table of contents"... that is an ordered list of pointers to positions in a file, aka offsets. Say that you have millions of records stored in a table, rather than search the table for matching criteria, it's much faster to reference an ordered list for matches, then stack the pointers to the specific matching rows. A perfect example of an index is a tables primary key field, most typically its "id" field. If you want row id # 11234566, it's much faster ask the index for a pointer to the data than it is to scan the data source for position 11234566.

Here's a not so obvious use of indexing:

CREATE TABLE activity_log (
datetime_created DATETIME
CREATE TABLE activity_log_to_date_key (
activity_log_id INT UNSIGNED NOT NULL,
date_created_key  INT UNSIGNED NOT NULL REFERENCES dim_datetime(id),
UNIQUE KEY(activity_log_id),
CREATE TABLE dim_datetime (

Your operation can create your log record, but then create a reference to an indexed datetime that is faster to search/sort than your log table. Then join back your log table on its own primary key. If you need me to expand on this, let me know. I hope this makes sense.

Sample query:

SELECT a.activity_log_id, al.activity_type_id, al.datetime_created
FROM activity_log_to_date_key a 
INNER JOIN dim_datetime d ON (d.id = a.date_created_key)
LEFT JOIN activity_log al ON (al.id = a.activity_log_id)
WHERE d.date_hour BETWEEN '2009-01-01 00:00:00' AND '2009-06-01 12:00:00';
| improve this answer | |
  • thanks, that's very clear ! In your example, will "PRIMARY" change the way the RDMBS stores the "offset", or is it just used for uniqueness constraints ? – Thomas Joulin Jan 4 '11 at 9:12

One point that a lot of people seem to miss is that a DBMS will often (or can only) only use one index per table reference in a query, and if it can and does use multiple indexes it would probably be faster to use a combined index if present.

For instance, if searching a large table for rows WHERE AnIntegerColumn = 42 AND AnOtherInt = 69 the fastest route to those rows would be an index on the two columns AnIntegerColumn and AnOtherInt. If you only have an index on each individually but no combined index the DB will either search one or the other index and separately filter the results with the second clause, or scan both and marry the results up afterwards.

Another common simple operation that can be improved with composite indexes is WHERE SomeColumn = <SomeValue> ORDER BY SomeOtherColumn - if there is an index on SomeColumn and SomeOtherColumn (in the right order) the filtering and ordering operations can be performed at the same time in some circumstances.

Adding too many indexes can be a bad optimisation of course, as the extra space used to store the indexes (and the IO-load to maintain them if your DB sees many write operations) may be a worse problem than the slightly less optimal read queries, so don't over-do it.

| improve this answer | |

David and Randy have this covered. I just wanted to add that the EXPLAIN command can be a huge help in figuring out when you will get a big saving out of creating an index, as well as suggesting which indexes are needed. It will display the steps that the database is taking to run your query, so you know which bits are taking the longest time.

| improve this answer | |
  • To add to Gaurav's answer, use "EXPLAIN EXTENDED", then immediately type "SHOW WARNINGS" to see how your query is translated. – randomx Jan 5 '11 at 17:46

Something I haven't seen mentioned yet on here is that when you have more than one disk you probably want to put your index on a different disk than where the data is actually at. This can speed some operations up. I think this deserves a question in it's own right tho.

| improve this answer | |
  • That used to be true but these days we say don't try to second-guess your I/O subsystem. You don't know where a storage array is going to place your data anyway. – Gaius Jan 5 '11 at 14:28
  • 1
    @gaius I rather meant if you didn't have a RAID5 (or the like) setup, to put the indexes on E:, the data on F:, etc. – jcolebrand Jan 5 '11 at 15:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.