The usefulness of MySQL indexes

MySQL index in MySQL database, can effectively improve the efficiency of the query, especially when the query data volume is very large, the effect is more obvious, often can make the query faster than tens of thousands of times.

MySQL indexing is a very important concept and the scope of application is very wide. So what does the MySQL index do? Below you will find the answers.

MySQL index function
In addition to the ordered lookups mentioned above, the database uses a variety of fast location techniques to greatly improve the query efficiency. In particular, when the amount of data is very large and the query involves more than one table, using an index can often speed up the query by tens of thousands of times.
For example, there are 3 unindexed tables T1, T2, T3, each containing only columns C1, C2, and C3, each containing 1000 rows of data, referring to the value of 1~1000, and the query that looks for the equivalent row of values is shown below.

SELECT c1,c2,c3 from T1,t2,t3 WHERE c1=c2 and C1=C3
The result of this query should be 1000 rows with 3 equal values per row. To process this query without indexing, you must look for all the combinations of 3 tables in order to derive those rows that match the WHERE clause. The number of possible combinations is 1000x1000x1000 (1 billion), and obviously the query will be very slow.
If you index each table, you can greatly speed up the query process. Queries using the index are handled as follows.

(1) Select the first row from the table T1 to see the data that this row contains.

(2) Use the Index on table T2 to directly locate the row in the T2 that matches the value of T1. Similarly, use the index on table T3 to directly locate rows in T3 that match the values from T1.

(3) Scan the next line of the table T1 and repeat the previous procedure until all the rows in the T1 are traversed.

In this case, a full scan is still performed on table T1, but the ability to index lookups on tables T2 and T3 directly takes rows from those tables, 1 million times times faster than unused indexes.
With the MySQL index, MySQL accelerates the search for the WHERE clause to satisfy the criteria row, while in a multi-table join query, speeds up matching rows in other tables when the connection is executed.

The usefulness of MySQL indexes