MySQL/Performance/Indexing

Introduction
When working with large databases, particularly where the sort order is different than the Proimary Key (for example in a staff table the StaffID is the stored order, however it is probably more common to list the staff in order of LastName then FirstName. Indexing the table on LastName + FirstName will significantly improve the performance of the Query (SELECT statement) for a large number of records)

Creating indexes
MySQL automatically creates an index on a primary key and in most cases this is fine (as the number of records in a table may be small).

If you have a large table (lots of records) and the SELECT clause seems to take a long time, the frst thing to look at is creating an index. For example, if you commonly have the SELECT sort the fields in a particular order (not Primary Key order) - like sorting by "LastName, FirstName" in a student table, it may be useful to create an INDEX using these fields.

where
 * FULLTEXT indexes text fields
 * length allows you to specify how many characters of a field to sort.

Example
For further reading see Welling & Thomson (2009, pp.234-235)