MySQL CREATE index

In a database table, indexing a field can greatly improve query speed. Suppose we create a mytable table:

Copy Code
The code is as follows:
CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL);

We randomly inserted 10,000 records, including one: 5555, admin.
In the Find username= "admin" record select * from MyTable WHERE username= ' admin '; If an index has been established on username, MySQL does not need any scanning, that is, the record can be found exactly. Instead, MySQL scans all records, that is, to query 10,000 records.
Index sub-column indexes and composite indexes. A single-column index, that is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index. A composite index, that is, a cable that contains multiple columns.
MySQL index types include:
I. GENERAL index
This is the most basic index and it has no limitations. It is created in the following ways:
1. Create an index

Copy Code
The code is as follows:
CREATE INDEX indexname on mytable (username (length));

If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.
2. Modify the table structure
Copy Code
The code is as follows:

ALTER mytable ADD INDEX [IndexName] on (username (length))-Specify directly when creating a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, INDEX [IndexName] (username (length)));
-Syntax for deleting indexes:
DROP INDEX [IndexName] on mytable;

Second, unique index
It is similar to the previous normal index, except that the value of the indexed column must be unique, but it allows for a null value. If it is a composite index, the combination of column values must be unique. It is created in the following ways:

Copy Code
The code is as follows:
CREATE UNIQUE INDEX indexname on mytable (username (length))
-Modify Table structure
ALTER mytable ADD UNIQUE [IndexName] on (username (length))
-Specify directly when creating a table
CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));

Third, primary key index
It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:

Copy Code
The code is as follows:
CREATE TABLE mytable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY KEY (id));

Of course, you can also use the ALTER command. Remember: A table can have only one primary key.
Iv. Combined Index
To visually compare single-column and composite indexes, add multiple fields to the table:
Copy Code
The code is as follows:
CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR (+) NOT NULL, age INT NOT NULL );

To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:

Copy Code
The code is as follows:
ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age); [Code]
When the table is built, the usernname 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 Usernname,city,age, 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:
Usernname,city,age usernname,city Usernname Why not city,age such a combination index? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined index:
[Code]
SELECT * FROM MyTable whree username= "admin" and city= "Zhengzhou" select * FROM MyTable whree username= "admin"

And the next few are not used:
Copy Code
The code is as follows:

SELECT * FROM MyTable whree age=20 and city= "Zhengzhou" select * FROM MyTable whree city= "Zhengzhou"

Five, the time to establish the index
Here we have learned to build an index, so where do we need to build the index? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like. For example:

Copy Code
The code is as follows:
SELECT t.name from MyTable T left joins MyTable m on T.name=m.username WHERE m.age=20 and m.city= ' Zhengzhou '

The city and age need to be indexed, because the userame of the MyTable table also appears in the join clause, and it is necessary to index it.
Just now it is only necessary to index the like at certain times. Because MySQL does not use indexes when querying with wildcards% and _. For example, the following sentence will use the index:
Copy Code
The code is as follows:

SELECT * FROM MyTable WHERE username like ' admin% '

And the following sentence will not be used:
Copy Code
The code is as follows:
SELECT * FROM MyTable wheret Name like '%admin '

Therefore, you should pay attention to the above differences when using like.
Six, the shortcomings of the index
above are said to use the benefits of the index, but excessive use of the index will cause abuse. So the index has its drawbacks as well:
1. Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, UPDATE, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file.
2. Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.
indexing is just one factor in efficiency, and if your MySQL has a large data size table, you'll need to spend time studying to build the best indexes, or refine the query statements.
Vii. Considerations for using Indexes
There are some tips and considerations when using indexes:
1. The index will not contain a column with null values
as long as the column contains null values will not be included in the index, as long as there is a column in the composite index with a null value, Then this column is invalid for this composite index. So we don't want the default value of the field to be null when the database is designed.
2. Use a short index
to index a string, and if possible, specify a prefix length. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.
3. Index column sort
The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
4.like Statement Operations
It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.
5. Do not perform operations on columns
Copy the Code
Code as follows:

SELECT * from the users where year (adddate) <2007; The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to: Copy code code as follows: SELECT * from Users where adddate< ' 2007-01-01 '; 6. Do not use not and <> operations
Above, the MySQL index type is introduced. We hope to help you.

MySQL CREATE index