GNU Info

(mysql.info)MySQL indexes

---

Next: Indexes Prev: Data size Up: Optimizing Database Structure

---

Enter node , (file) or (file)node

How MySQL Uses Indexes
----------------------

Indexes are used to find rows with a specific value of one column fast.
Without an index MySQL has to start with the first record and then read
through the whole table until it finds the relevant rows. The bigger
the table, the more this costs. If the table has an index for the
columns in question, MySQL can quickly get a position to seek to in the
middle of the data file without having to look at all the data. If a
table has 1000 rows, this is at least 100 times faster than reading
sequentially. Note that if you need to access almost all 1000 rows it
is faster to read sequentially because we then avoid disk seeks.

All MySQL indexes (`PRIMARY', `UNIQUE', and `INDEX') are stored in
B-trees. Strings are automatically prefix- and end-space compressed.
Note: `CREATE INDEX'.

Indexes are used to:
   * Quickly find the rows that match a `WHERE' clause.

   * Retrieve rows from other tables when performing joins.

   * Find the `MAX()' or `MIN()' value for a specific indexed column.
     This is optimized by a preprocessor that checks if you are using
     `WHERE' key_part_# = constant on all key parts < N.  In this case
     MySQL will do a single key lookup and replace the `MIN()'
     expression with a constant.  If all expressions are replaced with
     constants, the query will return at once:

          SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

   * Sort or group a table if the sorting or grouping is done on a
     leftmost prefix of a usable key (for example, `ORDER BY
     key_part_1,key_part_2 '). The key is read in reverse order if all
     key parts are followed by `DESC'.

     The index can also be used even if the `ORDER BY' doesn't match
     the index exactly, as long as all the unused index parts and all
     the extra are `ORDER BY' columns are constants in the `WHERE'
     clause. The following queries will use the index to resolve the
     `ORDER BY' part:

          SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3;
          SELECT * FROM foo WHERE column=constant ORDER BY column, key_part1;
          SELECT * FROM foo WHERE key_part1=const GROUP BY key_part2;

   * In some cases a query can be optimized to retrieve values without
     consulting the data file. If all used columns for some table are
     numeric and form a leftmost prefix for some key, the values may be
     retrieved from the index tree for greater speed:

          SELECT key_part3 FROM table_name WHERE key_part1=1


Suppose you issue the following `SELECT' statement:

     mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on `col1' and `col2', the appropriate
rows can be fetched directly. If separate single-column indexes exist
on `col1' and `col2', the optimizer tries to find the most restrictive
index by deciding which index will find fewer rows and using that index
to fetch the rows.

If the table has a multiple-column index, any leftmost prefix of the
index can be used by the optimizer to find rows. For example, if you
have a three-column index on `(col1,col2,col3)', you have indexed
search capabilities on `(col1)', `(col1,col2)', and `(col1,col2,col3)'.

MySQL can't use a partial index if the columns don't form a leftmost
prefix of the index.  Suppose you have the `SELECT' statements shown
below:

     mysql> SELECT * FROM tbl_name WHERE col1=val1;
     mysql> SELECT * FROM tbl_name WHERE col2=val2;
     mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on `(col1,col2,col3)', only the first query shown
above uses the index. The second and third queries do involve indexed
columns, but `(col2)' and `(col2,col3)' are not leftmost prefixes of
`(col1,col2,col3)'.

MySQL also uses indexes for `LIKE' comparisons if the argument to
`LIKE' is a constant string that doesn't start with a wild-card
character.  For example, the following `SELECT' statements use indexes:

     mysql> select * from tbl_name where key_col LIKE "Patrick%";
     mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

In the first statement, only rows with `"Patrick" <= key_col <
"Patricl"' are considered.  In the second statement, only rows with
`"Pat" <= key_col < "Pau"' are considered.

The following `SELECT' statements will not use indexes:
     mysql> select * from tbl_name where key_col LIKE "%Patrick%";
     mysql> select * from tbl_name where key_col LIKE other_col;

In the first statement, the `LIKE' value begins with a wild-card
character.  In the second statement, the `LIKE' value is not a constant.

Searching using `column_name IS NULL' will use indexes if column_name
is an index.

MySQL normally uses the index that finds the least number of rows. An
index is used for columns that you compare with the following operators:
`=', `>', `>=', `<', `<=', `BETWEEN', and a `LIKE' with a non-wild-card
prefix like `'something%''.

Any index that doesn't span all `AND' levels in the `WHERE' clause is
not used to optimize the query. In other words:  To be able to use an
index, a prefix of the index must be used in every `AND' group.

The following `WHERE' clauses use indexes:
     ... WHERE index_part1=1 AND index_part2=2 AND other_column=3
     ... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
     ... WHERE index_part1='hello' AND index_part_3=5
               /* optimized like "index_part1='hello'" */
     ... WHERE index1=1 and index2=2 or index1=3 and index3=3;
               /* Can use index on index1 but not on index2 or index 3 */

These `WHERE' clauses do *NOT* use indexes:
     ... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
     ... WHERE index=1 OR A=10                  /* Index is not used in both AND parts */
     ... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

Note that in some cases MySQL will not use an index, even if one would
be available.  Some of the cases where this happens are:

   * If the use of the index would require MySQL to access more than 30
     % of the rows in the table.  (In this case a table scan is
     probably much faster, as this will require us to do much fewer
     seeks).  Note that if such a query uses `LIMIT' to only retrieve
     part of the rows, MySQL will use an index anyway, as it can much
     more quickly find the few rows to return in the result.

Indexes

Column Indexes

Multiple-column indexes

Multiple-Column Indexes

Table cache

How MySQL Opens and Closes Tables

Creating many tables

Drawbacks to Creating Large Numbers of Tables in the Same Database

Open tables

Why So Many Open tables?