MySQL Index review notes

Index Type:

1. General Index

Create index index_name on test_table (column_name (length));

2, unique index, can have a null value

Create Unique Index  on test_table (column_name (length));

3, primary key index, can not be empty

4, combined index, follow the "leftmost prefix"

Create Index  on test_table (Column_name1 (Ten), Column_name2,column_name3);

When the table is built, the column_name1 length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.

If you set up a single-column index on Column_name1,column_name2,column_name3, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index, in fact, is equivalent to the following three sets of composite indexes:

(Column_name1,column_na

MySQL uses indexes only on <,<=,=,>,>=,between,in, and sometimes like.

MySQL Index review notes