How to properly create MYSQL database Indexes

It is fast enough to run a common MySQL with a small amount of data and traffic. However, when the data volume and traffic volume increase sharply, it is obvious that MySQL is slow or even down, we need to consider optimizing our MySQL. An important part of optimizing MYSQL is to create a correct and reasonable index for the database.
 
If no index exists, MySQL must scan all the records of the entire table from the first record until the required records are found. The more records in the table, the higher the operation cost. If an index has been created on the column used as a search condition, MySQL can quickly obtain the location of the target record without scanning any records. That is to say, indexes can greatly reduce the time for the database management system to query data.
What are the advantages of indexing?
 
1. By creating a unique index, You can ensure the uniqueness of each row of data in the database table.
 
2. Data retrieval speed can be greatly accelerated, which is also the main reason for index creation.
 
3. It can accelerate the connection between tables, which is particularly meaningful in realizing the integrity of data parameters.
 
4. When using grouping and sorting clauses for data retrieval, it can also significantly reduce the time for grouping and sorting in queries.
What are the disadvantages of indexing?
 
1. It takes time to create and maintain indexes. This time increases with the increase of data volume.
 
2. In addition to the data space occupied by data tables, each index also occupies a certain amount of physical space. To create a clustered index, the required space will be larger.
 
3. When adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed.
What types of indexes are available?
 
1. Common Index
 
This is the most basic index type, and it has no limitations such as uniqueness.
 
2. Unique Index
 
This index is basically the same as the previous "normal index", but there is a difference: all values of the index column can only appear once, that is, they must be unique.
 
3. Primary Key
 
It is a special unique index and does not allow null values.
 
4. Full-text index
 
MySQL supports full-text indexing and full-text retrieval from version 3.23.23.
Single Column index and combined index:
 
A single-column index is to create an index on a single field.
 
A composite index is an index created in two or more columns. When you search for two or more columns as a key value, it is best to create a composite index on these columns.
Notes for creating and using indexes:
 
1. The index should be created on fields that frequently perform select operations. This is because, if these columns are rarely used, the query speed cannot be significantly changed with or without indexes. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.
 
2. The index should be created on a field with unique values. This is the best way to make full use of indexes ., For example, the id field of the primary key and the unique name field. If the index is based on a field with a few unique values, such as gender field and few category fields, the new index has almost no significance.
 
3. Indexes should not be added for columns defined as text, image, or bit data types. Because the data volume of these columns is either large or the value is small.
 
4. When the modification performance is much higher than the retrieval performance, you should not create an index. The modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index.
 
5. You must create an index for the columns that appear in the WHERE and JOIN operations.
 
6. the MySQL index is invalid when the query starts with the wildcard "%" and. However, this index is valid: select * from tbl1 where name like 'xxx % ', so it is important to write your SQL statement carefully.
 
 
 
An example is used to describe the differences between a single index and a composite index, as well as some details about index usage.
 
Create a table:
Create table myIndex (
 
I _testID INT NOT NULL AUTO_INCREMENT,
 
Vc_Name VARCHAR (50) not null,
 
Vc_City VARCHAR (50) not null,
 
I _Age INT NOT NULL,
 
I _SchoolID INT NOT NULL,
 
Primary key (I _testID)
 
);
 
Five vc_Name = "erquan" records are distributed in the Top 7 and bottom 8 of the 10000 records, except that the combinations of city, age, and school are different.
Look at this T-SQL:
SELECT I _testID FROM myIndex WHERE vc_Name = 'erquanc' AND vc_City = 'zhengzhou' AND I _Age = 25;
 
First, create a single column index:
An index is created on the vc_Name column. When the T-SQL was executed, MYSQL quickly locked the target to five records in vc_Name = erquan and pulled them out to the intermediate result set. In this result set, records with vc_City not equal to "Zhengzhou" are excluded, records with I _Age not equal to 25 are excluded, and a unique matching record is filtered out.
 
Although an index is created on vc_Name, MYSQL does not need to scan the entire table during query, which improves the efficiency, but there is still some distance from our requirements. Similarly, the efficiency of single-column indexes created in vc_City and I _Age is similar.
 
To further extract MySQL efficiency, you must consider establishing a composite index. Vc_Name, vc_City, and I _Age are built into an index:
Alter table myIndex add index name_city_age (vc_Name (10), vc_City, I _Age); -- note that when creating a TABLE, the length of vc_Name is 50. Why is 10 used here? In general, the length of the name will not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.
 
When you run the T-SQL, MySQL finds a unique record without scanning any record !!
 
Someone must ask, if you create a single column index on vc_Name, vc_City, and I _Age respectively, so that the table has three single column indexes, the query efficiency is the same as that of the preceding composite index? Hey, big difference, far lower than our combined index ~~ Although there are three indexes at this time, MySQL can only use one of them, which seems to be the most efficient single-column index.
 
The establishment of such a composite index is actually equivalent to the establishment
Vc_Name, vc_City, I _Age
Vc_Name, vc_City
Vc_Name
These three composite indexes! Why are there no composite indexes such as vc_City and I _Age? This is because mysql Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. Not as long as the query contains these three columns will use the composite index, the following T-SQL will use:
SELECT * FROM myIndex WHREE vc_Name = "erquan" AND vc_City = "Zhengzhou"
SELECT * FROM myIndex WHREE vc_Name = "erquan"
The following are not used:
SELECT * FROM myIndex WHREE I _Age = 20 AND vc_City = "Zhengzhou"
SELECT * FROM myIndex WHREE vc_City = "Zhengzhou"