Summary

Poor use of indexes is probably the single most important cause of performance problems. An index is a small, sorted file that points to the main data file. Finding a particular record is then quicker because only the small index file has to be searched.

An index can be a primary key (a unique index that cannot contain nulls), a unique index, an ordinary index (that can contain duplicates), or a full-text index. Full-text indexes allow a high level of sophistication in searching text fields for certain combinations of keywords.

Auto increment fields are associated with the primary key and allow MySQL to automatically take care of the sequencing of the field. If a record is inserted, MySQL will add 1 to the previous auto incremented value and use this for the value of the inserted auto increment field.

EXPLAIN returns useful information about how MySQL uses indexes in a particular query. You can use it to see whether MySQL is using the indexes you've created, and if the query is not optimal, get information about what fields to create indexes on or how to change the query to make it more optimal.