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.
Indexes on Tables
Indexes on Tables
Join the DZone community and get the full member experience.Join For Free
Learn how to create flexible schemas in a relational database using SQL for JSON.
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 column.
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.