![[Main Page]](/modules/mediawiki/images/mediawiki.png)
Database indexes are used to find rows with specific column values fast. Without an index, MySQL has to start with the first record and then read through the whole table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This can cut the query time dramatically, which ultimately means your page will load faster.
Contents |
When to use an index
Indexes improve the performance of SELECT queries to quickly find the rows that match WHERE or ORDER BY clauses. If the primary key is the only column that is used in the clause then you don’t need an index, as the default behaviour of a table is to be sorted on the primary key. If you mostly access a table by searching on a (not primary key) column or combination of columns, it might be time to start thinking about using indexes.
Which columns to use in the index key
When creating an index first think about the column that is used most often in queries, and make that the first index key.
e.g. “INDEX example_index (col_3)†would be used if col_3 is often used to access the table.
If the database tables are accessed using that column plus others, make the other columns the next keys in the index, in order of frequency of use.
e.g. “INDEX example_index (col_3, col_5, col_6)â€
Index keys should be created with the most used columns starting from the left so that the same index can be used for patial keys. For example, in the above example the index will be used when the table is accessed by (col_3), (col_3 and col_5) and (col_3, col_5 and col_6).
So SELECT * FROM example_table WHERE col_3=val1 AND col_5=val2; will use the index
and SELECT * FROM example_table WHERE col_6=val; will not use the index, as col_6 does not match the leftmost index key.
Disadvantages of using indexes
MySQL stores row data and index data in separate files. So for every index you create, you will be adding to the size of the database. Separate files makes data retrieval very quick, but slows down data storage, as the database now needs to make changes to more than one file for every INSERT, UPDATE or DELETE statement.
Therefore, think carefully about how often you would use the index before creating one and only create the indexes that you really need.
Creating a table with an index
You can create an index at the same time that you create a table in the mysql.sql file. MySQL accepts two commands for creating an index: ‘KEY’ and ‘INDEX’. Either one could be used as they perform exactly the same function.
Examples: CREATE TABLE `example_table` (
`eg_id` int(12) unsigned NOT NULL auto_increment, `eg_integer` int(10) unsigned NOT NULL default '0', `eg_string` char(100) NOT NULL default , `eg_date` int(12) unsigned NOT NULL default '0', PRIMARY KEY (`eg_id`), KEY `eg_date` (`eg_date`,`eg_string`), KEY `eg_integer` (`eg_integer`)
) TYPE=MyISAM;
CREATE TABLE `example_table` (
`eg_id` int(12) unsigned NOT NULL auto_increment, `eg_integer` int(10) unsigned NOT NULL default '0', `eg_string` char(100) NOT NULL default , `eg_date` int(12) unsigned NOT NULL default '0', PRIMARY KEY (`eg_id`), INDEX `eg_date` (`eg_date`,`eg_string`), INDEX `eg_integer` (`eg_integer`)
) TYPE=MyISAM;
All column types can be indexed, but when indexing a BLOB or TEXT column you must specific the length. e.g. CREATE TABLE test (
text_col text, INDEX (text_col(10))
) TYPE=MyISAM;
Adding an index to an existing table
Example: ALTER TABLE example_table
ADD INDEX [index_name] (index_col_name,...)
References:
MySQL Documentation:
