Quick note about MySQL Index.
Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire 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 is much faster than reading every row sequentially.
Most MySQL indexes are stored in B-trees. But indexes on spatial data types use R-trees;
INDEX. Duplicate values or composites are allowed.
UNIQUE INDEX. Duplicate values or composites are not allowed.
PRIMARY KEY. Special unique index. Only one in each table.
Composite Index is multiple-column index. For example:
This composite index, in fact, is equivalent to the following three indexes:
But NO indexes like:
Because of leftmost prefix. It could simply be considered as composite from left. So the following select will benefit from this composite index:
The following will not use it：
A unique index enforces the constraint that we cannot have two equal values in that column. When unique with composite index, no two equal composites are allowed.
The last insert fails because it violates the unique index on column foo when it tries to insert the value 1 into this column for a second time.
UNIQUEconstraint allows multiple NULLs.
PRIMARY KEY is a special situation of
But some points different:
PRIMARY KEYimplies NOT NULL, but unique index can be nullable.
- There can be only one
PRIMARY KEY, but there can be multiple unique indexes.
- If there is no clustered index defined then
PRIMARY KEYwill be the clustered index.
Three ways to create index
- CREATE TABLE
- ALTER TABLE
- CREATE INDEX (Not allowed to create
Delete a column from a table, index will also be influenced. For composite index, if one column inside the index is removed, the composite index will also remove it from its compostes. What’s more, if all columns from this index are removed, the composite index will be removed entirely.
Adding index increases query speed. But:
- It decreases also speed to update, insert or delete.
- Mysql comsumes more resources to maintain indexes
- Indxes files occupy more spaces on disk
So we need to know how to choose columns to set index and also optimize queries.
How to choose columns for index
Consider about columns as conditions in queries frequently called.
Consider about columns in
So we could consider about setting index for age, city in
WHERE or username in
JOIN. Only one index can be used per SELECT/statement in the query
Index works with these operators <,<=,=,>,>=,between,in and like (Only for expression not beginning with% or _).
Not suggest columns which will be updated, inserted or deleted too frequently.
Better to choose columns with big cardinality, so different values. It is easy to distinguish birthday with differentdates. It means nothing set index on gendar to distinguish gendar with only ‘M’ or ‘F’. Because always half will be left.
We could compte Index Selectivity to help us to choose.
The higher the index selectivity value the more suggested to choose.
In composite index, consider about leftmost, column with big index selectivity should be on left.
Consider table with many records, for example, 2000 records.
Consider short index to reduce size of index. Sometimes we don’t need to index on the entire field, we could set a prefix length. For exampe, a column is CHAR(200), if in the first 10 characters, most records are unique.We could use short index:
So mysql only indexes according to first 10 characters which involves less disk I/O, comsumes less index spaces, so may increase query speed.
Index does not work with <>,not in, !=.
Do not calculate on columns, it will make index invalid.
- Avoid to use
NULLas default value for indexed column.