Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

closed as not a real question by jcolebrand Nov 18 '11 at 7:00

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
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

4 Answers 4

up vote 10 down vote accepted

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 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
activity_type_id SMALLINT UNSIGNED NOT NULL,
datetime_created DATETIME
KEY(activity_type_id),
PRIMARY KEY(id)
);
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),
KEY(date_created_key)
);
CREATE TABLE dim_datetime (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
date_hour DATETIME NOT NULL,
PRIMARY KEY(id),
KEY(date_hour)
);

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';
share|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.

share|improve this answer
    
To what RDMSs does the one-index-per-table limit apply? –  Jon of All Trades Mar 7 '12 at 18:45
1  
@Jon: The vast majority in most cases by my understanding. MSSQL will sometimes use two indexes for two ANDed WHERE/ON clauses (if you look at the query planner output you'll see them with a row-match after) but it will only do this if both index seeks have a selectivity exception that is in a fairy tight range (if one is very selective just using it is more efficient, if neither are very selected then using either is better than trying to use both and getting a massive intermediate result). IIRC MySQL will never use two indexes in this way. I can't speak from direct experience for others. –  David Spillett Mar 12 '12 at 17:10
    
Interesting, a quick test on MS SQL 2K8 confirms that behavior. In a large table with very-high-selectivity ID columns, each indexed, an execution plan for X = 123 uses the X index, a query for Y = 456 uses the Y index, but X = 123 AND Y = 456 uses only the X index. That's very important to know. I am educated, thank you! –  Jon of All Trades Mar 12 '12 at 19:23
    
Just correcting myself: "selectivity exception" in my comment above should be "selectivity expectation." –  David Spillett Mar 13 '12 at 8:48

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.

share|improve this answer
    
To add to Gaurav's answer, use "EXPLAIN EXTENDED", then immediately type "SHOW WARNINGS" to see how your query is translated. –  randy melder 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.

share|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
    
@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.