In actual services, most complex queries are join queries. To make a join query, ensure that
values of the join column are unique in at least one of the two tables, that is, one-to-one or
many-to-one join. Otherwise, there will be a many-to-many join, which will exponentially
increase join records.
Use a complex query in the XX project as an example. The query joins two big tables by the
Internet cafe ID column, while then an Internet cafe ID corresponds to 144,372 records in the
left table and 2,357,685 records in the right table. In this case, the number of join and
comparison times for this Internet cafe ID reaches 340 billion (144372 x 235768).
This is because no join condition is specified in the join statement. In the above example, if a
web browsing period column is specified as a condition, the complexity of join computing
will be significantly reduced, and the query execution time will be reduced from 29 hours to
less than 1 minute.
Note that join records in a join statement will be further filtered. Therefore, an exponential
increase in join records does not necessarily lead to an error in final join results. This makes
performance problems caused by many-to-many join hard to discover when there is a small
amount of test data.
It is recommended that developers check the uniqueness of a join column before composing a
join query statement. This helps avoid many-to-many join queries.
