Indexes on Tables
Join the DZone community and get the full member experience.Join For Free
Increasingly I find a very binary split between the professionals I come into contact with. One group of people are very database-aware and take the design of their storage quite seriously - with good results. The other group are more concerned with the functionality of their application, and have little regard for how it is stored other than considering it a keeping-place and making useful table and column names.
Primary KeysEvery table needs a primary key - this a unique key and will often be an
auto_increment column, where every new record gets a new number
incrementally. However a primary key can be any other unique piece of
data in the table, for example a product SKU or a book ISBN.
IndexesA general rule of thumb is to add indexes on columns which are used in a
where clause - in layman's terms, adding the index means mysql keeps
track of the data in that column and can select using it as a criteria
much more easily. If, for example, you always select a list of
employees by their department, you'd add an index on the department
Composite IndexesIf you always select by two columns then you can add a composite index
across both columns, so that mysql can use that index when it runs a
select searching for both criteria. Be aware though that although the
index can be used for a select statement involving one of the columns,
this only applies to the first one - if you want to select by just the
second one then you need to add an additional index on just one column.
Which Index Does MySQL Choose?MySQL will try to guess which is the best index to use - in the next post in this series we'll look at EXPLAIN to work out what indexes are being used on a given query - this can help us identify which indexes are useful to add.
Published at DZone with permission of Lorna Mitchell, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.