From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL <


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)

Icon Objective.png

Objective MySQL Using INDEX to improve query (SELECT) Performance

By the end of this page you will be able to:

  • Describe the purpose of INDEXING a table
  • Implement a basic INDEX on a table.

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.

Icon Notes.png

The Bakus-Naur format is:

ON table_name (index_column_name[(length)] [ASC|DESC], ...])


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


CREATE INDEX ndxStuName ON tblStudent (stuLastName,stuFirstName);

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

Icon References.png References

  1. (Welling,L. & Thomson, L. (2009) PHP and MySQL Web Development. 4th Ed. Addison-Wesley

MySQL/Performance/Indexing. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 12, 2017, from    (zotero)