|Table of contents|
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)
Objective MySQL Using INDEX to improve query (SELECT) Performance
By the end of this page you will be able to:
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.
The Bakus-Naur format is:
CREATE [UNIQUE|FULLTEXT] INDEX index-name
- FULLTEXT indexes text fields
- length allows you to specify how many characters of a field to sort.
For further reading see Welling & Thomson (2009, pp.234-235)
- (Welling,L. & Thomson, L. (2009) PHP and MySQL Web Development. 4th Ed. Addison-Wesley