Index-based MySQL optimization

Check out the MySQL slow query log today to see a four-table associated SQL operation that takes 1006s of time. This is also based on the idea of sub-query, the above table is optimized. Reduce time complexity to the n^2 level. But after the optimization time is three times times more than the original.

Original SQL statement:

Explain select distinct B.businessid from
Gte_order O,
        Gte_jy_contract C,
        Flow_ru_participant TP,
        Flow_ru_business b
        where B.businessid = O.businessid
and O.businessid=c.businessid
and B.businessid = Tp.businessid
and O.citycode = 120000
and Tp.status = 0
and O.status in
(
                -1,6,7
)
Order BY O.createtime;

time-consuming 1s,

The optimized statement:

#3 S 334 ms
DESC SELECT DISTINCT Tempb.businessid #distinct导致temportory
From (select O.businessid,o.createtime
From Gte_order o,gte_jy_contract C
      where O.businessid = C.businessid and O.status in ( -1,6,7) and O.citycode = 120000) as Tempa, (select B.businessid
From Flow_ru_participant p,flow_ru_business b
      where B.businessid = p.businessid and p.status = 0) as TEMPB
where Tempa.businessid = Tempb.businessid
Order by Tempa.createtime; #filesort

Time-consuming 3s.

After reviewing its execution plan:

Modified Execution Plan:

Because of the existence of the index: the original SQL will only traverse its own index (the behavior involved in 72426), and then one time to query the index of the other three tables (the behavior involved 1,1,3), so its time complexity is O (n) level.

However, the method of sub-query, the first two tables directly query the time complexity of O (n), two tables derived from the temporary table between the query, the time complexity of O (n), the existence of the index to the time to reduce the complexity of O (n). The total time complexity is approximately 3*o (n), which is the result of time query time.

Summary:

For SQL queries, indexes are the most important way to optimize their efficiency, but because of the shortcomings of the index itself and its limitations, the index of the data table is limited and the functionality is limited. So it needs to be assisted in other ways. However, when writing SQL statements, you should first consider the case of indexes.

such as: How to build the index, how to use the index, no index how to optimize.

Another: About SQL timeout issues, regardless of SQL, guess is the reason for the network.

Index-based MySQL optimization