MySQL/Performance/Indexing

From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL <

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)


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:

CREATE [UNIQUE|FULLTEXT] INDEX index-name
ON table_name (index_column_name[(length)] [ASC|DESC], ...])

where

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

Example

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 http://www.virtualmv.com/wiki/index.php?title=MySQL/Performance/Indexing    (zotero)