MySQL index usage

MySQL index is an important concept and has a wide range of applications. So what is the role of the MySQL index? We will take you to the answer below.

MySQL index function
In addition to the ordered search mentioned above, the database can greatly improve the query efficiency by using various fast locating technologies. In particular, when the data volume is very large and the query involves multiple tables, using indexes often speeds up the query by thousands of times.
For example, three unindexed tables t1, t2, and t3 contain only columns c1, c2, and c3. Each table contains 1000 rows of data, which is 1 ~ The value of 1000 is as follows.

SELECT c1, c2, c3 FROM t1, t2, t3 WHERE c1 = c2 AND c1 = c3
The query result should be 1000 rows. Each row contains three equal values. To process this query without an index, you must search for all the combinations of the three tables to obtain the rows that match the WHERE clause. And the possible number of combinations is 1000 × 1000 × 1000 billion). Obviously, the query will be very slow.
If you index each table, the query process can be greatly accelerated. The index query process is as follows.

1) Select the first row from Table t1 to view the data contained in this row.

2) use the index on table t2 to directly locate the row in t2 that matches the value of t1. Similarly, you can use the indexes on table t3 to directly locate the rows in Table t3 that match the values from t1.

3) scan the next row of table t1 and repeat the previous process until all the rows in Table t1 are traversed.
In this case, a full scan is still performed on table t1, but the rows in these tables can be directly retrieved through index search on table t2 and table t3, which is 1 million times faster than when no index is used.
Using MySQL indexes, MySQL accelerates the WHERE clause's search for rows that meet the condition conditions, while multi-table join queries speed up row matching with other tables during connection execution.
 

Insufficient mysql Indexes

MySql connection string description

Three statuses of mysql triggers

How to Create a master-slave server for mysql

Obtain the MySql time function of the current time