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.

Can someone give me a brief intro to indexes? My questions:

  1. Why would I want to use indexes?
  2. How I should use them? It looks like I can create multiple indexes on a single table - what is that for?
  3. How can I find out if I need to use them?
  4. Are indexes created automatically for me with InnoDB?
share|improve this question

migrated from serverfault.com Jun 7 '12 at 2:33

This question came from our site for professional system and network administrators.

add comment

1 Answer 1

You don't, initially you want to create your table relationships and infrastructure without the indexes.

This is because until you know what how often you query a table, and the various queries, how would you define the BEST index? Indexes only speed up queries that intend to retreive data, more specifically for dealing with large volumes of data.

I would revise them, only when things begin to "slow down".

Primary Keys - define your Primary unique index for a table. Auto Increment fields are generally your primary keys, sometimes you may have a joiner table with a Many to Many relationship, where you don't want duplicate records so you'd make ALL fields the primary key.

For Example: parent_id is 1 and child_id is 1, but you don't want more than 1 record in the joiner table with 1 relating to 1.

Foreign Key indexes (keys that relate to another table), which are for integrity. I would recommend using these when creating your tables. Cascade updating and deleting is used to ensure when you delete a parent record, the children records associated are automatically deleted.

Although you assign your foreign key constraints to a "child" table referencing it's parent, when executed MySQL recognises "When item X is deleted from Parent_Table, we shall delete ALL records from Child_Table with a parent_id of X" - I hope this makes sense.

Index keys - are used to speed up queries which collect records, or even joining records from related tables, and sub-queries (Although I discourage the use of these).

FYI - InnoDB is a transactional database, thus unlike MyISAM, will ONLY lock an individual ROW when modifying a table (inserting, deleting, updating records). MyISAM locks the entire table when modifying records within it (hense why data importing large volumes of data to a MyISAM is discouraged).

IMO - You should use foreign key constraints, in INNODB tables. Ensure your tables have primary keys where necessary at most. Then when you're done, look at your data and keep a constant eye on how slow the queries become, then consider the best index forward from there.

share|improve this answer
    
Wonderful response - can you provide tips on how to keep an eye for slow queries and what type of indexes I would need to handle a variety of queries? –  Kirk Jun 6 '12 at 23:47
    
When you have a table, for instance which holds products. When you start off searching for products and you can collect your related results, within say, 1-2 second(s)... after a few weeks, or months (depending on growth) if you were to find that same search takes 6+ seconds. Then you should start looking at how to speed it (i.e. index them). This is because if you have < 1000 records, it takes longer to insert, update, delete records - it's more a hinderence. You probably won't notice the difference indexing early or not, but you may find it's not the best index later on. –  Ash Jun 6 '12 at 23:55
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.