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.

As a theoretical database question, if you are asked 'which indexes would you propose and why?' are the answers from primary, clustering and secondary index? How would we know if we should introduce one of those vs a B-Tree?

This should be from a simplistic indexing perspective, as I am asking due to course material related to indexes, where I have covered some introductory material.

share|improve this question
1  
So for which database would you like the answer. Each one of these are slightly different on the database. MySQL also has different table engines which implements indexing different internally depending on the engine. –  Namphibian Jun 7 '12 at 8:07
    
Could it be a general case? –  Tony Jun 7 '12 at 18:31
    
I will put a answer together with some general outlines as to when and how you would index and the differences between certain types of indexes. Just keep in mind that with databases there is no hard and fast rules. –  Namphibian Jun 8 '12 at 6:21
    
Thanks! Looking forward :) –  Tony Jun 8 '12 at 6:54
add comment

1 Answer

Virtually all MySQL indexes are BTrees.

All of MyISAM's indexes are separate from the data, hence not clustered.

InnoDB's PRIMARY KEY is "clustered" with the data. Hence a "range scan" on the PK is very efficient.

InnoDB's secondary keys include the PK. Hence, a secondary key lookup requires a second lookup to get to the data.

Given a SELECT, we can discuss what index to make that would optimize the query. The answer almost never mention primary, clustering, secondary or BTree. It will list the field(s) that should be in the INDEX, and which order they should be in.

Separate from that question, InnoDB tables should always have a PRIMARY KEY. This should be the 'natural' key or an artificial AUTO_INCREMENT. Again, knowing the SELECT(s) is necessary to be more specific.

To create the optimal index(es), all the important SELECTs, DELETEs, and UPDATEs, including JOINs, need to be discussed simultaneously.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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