Index usage principles in mysql

Using indexes in mysql to create indexes for tables is undoubtedly one of the best Optimization Methods for databases. The following is a summary of the index. Mysql query Optimizer's choice of indexes 1. Even if one SQL statement can select multiple indexes, it will select one index in most cases and discard other indexes. Prerequisites for using the index: 1. The index is stored after the where condition. 2. Different storage engines in MYSQL treat indexes a little differently. Common Index types and situations prefix indexes (also called short indexes) are used to index string columns. If possible, you should specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations. Sample Code: // create index ix_test on t (col (200) // SELECT userName, income FROM t WHERE col like 'John % '; that is: the application scope of short indexes is the first non-'%' instance after like. For example, a statement for a combined index is as follows: select * from users where area = 'beijinging' and age = 22; if we create a single index on area and age respectively, because mysql queries can only use one index at a time, the full table scan improves the efficiency even if no index is used, however, creating a composite index on the "area" and "age" columns will increase the efficiency. If we create a composite index (area, age, salary), it is equivalent to creating (area, age, salary), (area, age), (area) three indexes, which are called the best left prefix feature. For composite indexes, the MYSQL database queries the principle: select * from users where area = 'beijinging' and age = 22; select * from users where area = 'beijinging '; if area has an index, all the preceding SQL statements use an index. Because the combination index has the best left prefix, select * from users where age = 22; then the index is not used. Therefore, when creating a composite index, we should place the columns that are most commonly used as restrictions on the leftmost and decrease in turn. Single-key index or composite index can filter more than 90% of data in most scenarios, as long as it is not a filtering field ), other filter fields are frequently updated, and are generally more inclined to create composite indexes, especially in scenarios with high concurrency. Because when the concurrency is high, even if only a small IO consumption is saved for each Query, the total amount of resources saved due to the large execution volume is still very considerable. Of course, creating a composite index does not mean that you need to put all the fields in the Query condition in one index. You should also try to make an index be used by multiple Query statements, minimize the number of indexes on the same table, reduce the Index Update cost caused by data updates, and reduce the storage space consumed by indexes. If there is OR that uses indexes in SQL statements, if there is or, the where clause must be followed by an index with comprehensive conditions. Otherwise, the index will not work, therefore, you must create an index for all or fields. SELECT * FROM test WHERE last_name = 'widenius 'OR first_name = 'Michael'; ordery by and index sorting mysql queries only use one index, therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. (The sorting function is completed by the index.) Try not to include the sorting of multiple columns, if you want to create a composite index for these columns.