Comparative analysis of MySQL multi-table association on and where speed source code

Let’s talk about the conclusion first

The join speeds of Where and equivalent queries are basically the same, and the joins of non-equivalent queries are generally slower.

Experiment 1: There are no unequal records

We have two tables, namely member and member_class. The data structure is as shown below, where member.class_id and member_class.id are related fields

We wrote 200,000 pieces of data to the two tables respectively, of which member.class_id must exist in member_class.id (not There are unequal records)

Check the full table

In order to make the experiment more accurate, we query each result 20 times:

Where query times are (seconds):

0.253, 0.256, 0.256, 0.252, 0.257, 0.252, 0.260, 0.265, 0.253, 0.252, 0.254, 0.257, 0.254 , 0.257, 0.243, 0.250, 0.252, 0.252, 0.255, 0.284

onThe query time consumption is (seconds):

0.247, 0.260, 0.250, 0.246, 0.271, 0.247, 0.251, 0.247, 0.243, 0.247, 0.247, 0.245, 0.249, 0.246, 0.247, 0.253, 0.248, 0.254, 0.25 1, 0.247, 0.250

where query The average is 0.2557s, and the average on query is 0.2498s; the two are basically the same, and the difference is almost negligible.

Experiment 2: There are unequal records

We create a new member_v2 table, in which only 20% of the class_id exists in member_class.id

As shown below:

We continue to query the full table test:

We can see a very obvious gap. Due to the large number of unequal records in LEFT JOIN, When a large number of NULL values ​​appear, the query efficiency of the intermediate table becomes lower. Selecting WHERE query can automatically filter unequal records during query.

So the query time is also faster.

The above is the detailed content of Comparative analysis of MySQL multi-table association on and where speed source code. For more information, please follow other related articles on the PHP Chinese website!