How should I use indexes with MySQL?

Question

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?

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.