How to create an index· Indexes are usually used to increase the search speed of data rows matching other tables in the where condition or performing join operations. Therefore, we usually select columns that appear in the WHERE clause, join clause, order by, or group by clause as index columns. · When selecting an index, you must consider the base of the data column. The base number refers to the number of different data contained in the index data column. If the base number is higher than the number of rows in the data table, the indexing effect is better. In other words, the more different values of the index data column, the better the index effect. If a data column contains only two values: 0 or 1, the index is of little use. If the probability of a value appearing is almost equal, half of the data rows may be obtained no matter which value is searched. In these cases, it is best not to use indexes at all. · If the index value is short, the selected data type should be as small as possible. For example, if text can meet the requirements, we do not need to use mediutext. · If a joint index is created, for example, for T1, T2, and T3, an index is also created for T1, T1, and T2. However, if the values of T2, T3, t1t3, and t2t3 are specified separately, no index will be used.
How to avoid the absence of indexes in SQL statementsFirst, let's take a look at the working principle of the MySQL optimizer: The main goal of the MySQL query optimizer is to use the index as much as possible and use the strictest index to eliminate as many data rows as possible. Therefore, when we submit a query statement, the faster the optimizer can exclude non-conforming data, the faster the query result. · Try to compare columns with the same data type. For example, int and bigint are different. Char (10) is considered as char (10) or varchar (10), but is different from Char (12) or varchar (12. · Try not to use expressions or functions for index columns in the WHERE clause. If you use function calls or more complex arithmetic expressions in the index column, MySQL does not use the index because it must calculate the expression value of each data row. Where mycol <4/2 use index
Where mycol * 2 <4 does not use an index. When using like, do not use wildcards at the beginning. Where col_name like '% string %' Where col_name like 'abc % '· do not use type conversion. If an index column is of the int type and the value is of the limit type during query, no index can be used. Select * From mytbl where num_col = 1; use index
Select * From mytbl where num_col = '1'; no index is used. You can use the straight_join keyword in the SELECT statement to reload the optimizer selection. Select straight_join... from T1, T2, T3 ...;
Select... from T1 straight_join T2 straight_join T3 ...;
