MySQL Index Introduction

Indexing a field in a data table will greatly increase the speed of the query:

For example: SELECT * FROM mytable where username= ' admin ' if an index is established on the column username, only one time is required to find the record

One, the type of MySQL index:

1. General Index

Created: Create index indexname on MyTable (username)

Delete: Drop index [indexname] on MyTable

2. Unique index

Feature: Index column values must be unique, but null values are allowed

Created: Create unique index indexname on mytable (username)

3. Primary KEY index

Feature: Index column values must be unique and null values are not allowed

4. Combined Index

Created: Create index indexname on MyTable (id,username)

Second, the time to use the index

Generally, the columns that appear in the where and join are indexed

But mysql only in the = < <= > >= between in and some times like using the index

Third, the disadvantage of using the index

1, reduce the speed of adding and deleting changes

2. Index file takes up disk space

Iv. Use of indexing considerations

1. The index does not contain a column with null values, so do not set the default value of the column to NULL when you build the table

2. Use short index

3. The MySQL query uses only one index, and if the field uses an index in where, the index is not used in order by

4, using like '%aa% ' will not use the index and like ' aa% ' will use the index

5, do not operate on the index column, which will result in index invalidation, full table scan

6. Do not use not in and <> operation

MySQL Index Introduction