How should I use indexes with MySQL? Can someone give me a brief intro to indexes? My questions:


Why would I want to use indexes?
How I should use them? It looks like I can create multiple indexes on a single table - what is that for?
How can I find out if I need to use them?
Are indexes created automatically for me with InnoDB?
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.