MySQL Query two

Natural Connection Query

From the above figure we notice that the natural connection syntax is joinon, as is followed by the corresponding table and field aliases,join is followed by the connected table , on is followed by the conditions of the connection.

We query the amount of data is 16,000, we use the table engine is MyISAM.

We look at the time of this query is:4.527ms (this query is a simple query, no index)

We index the fields on the on condition to be:3.507ms

We built a composite index back there.

Let's look at another scene with only the staff_id and rental_id scenarios:

Query time is:3.219ms

We now add the three fields

The query time is:1.954ms

Why this is so magical, the code is a different field, the query time difference is very large? Let me explain: the composite index that we create, if the index takes effect will appear from left to right once the corresponding field, can not be empty, such as 123, you write a 13 interval, the index is invalid. There is another situation where the 12 index is also in effect. This behavior occurs because the index does not take effect.

Let's look at all of a sudden query

Time taken:7.126ms

Turn him into a natural connection.

Time taken:1.160ms

Summarize:

1, we have to give the natural connection check on the back of the word Chegasso attracted to improve the efficiency of the query, note that if it is a composite index, then we query the field order must follow the compound index from left to right order.

2, we compared the sub-query and natural connection query, they are still very big difference, the optimization method is that we translate the query into a natural connection query.

3, natural connection query when more must be to the table and the field alias, in order to facilitate the subsequent query. If this is not the table name plus field, it is too long. SQL statements look messy.

Left connection Query

Let's look at the effect of the query

This time we will find that the other column is empty, which is why? Because the left connection query is based on the left table, the left table has all the information found, not by the empty representative;

Fields for left table

Fields of the right table

This time we found that the left table has a value, the right table does not, this time we are the main left table, so we can not find the empty instead.

Let's look at the query

Time to query:2.470ms (not indexed)

Indexed query time:1.160ms

Summarize:

1, left JOIN query is based on the left table, if the right table does not have data to replace with empty;

2, the field after the ON clause plus index, can improve the efficiency of the query;

Right Connection query

Left table field (value without customer_id = 3)

Right table field:

Summarize:

1, the right connection query to the right table is the main, the left table does not have values, use empty instead.

2, in the field after the add index to improve the efficiency of the query;

Federated Queries

Merges the results of two queries together.

Let's look at a scenario where the conditions of the two queries are written as follows:

Will merge the results of the query together;

Union All

is not merging the same results

Summarize:

1, joint query is to merge the results of several queries together;

2. The union query merges the results of the query together, and union all is not merged;

3, UNION ALL efficiency is higher than the Union, the main reason is the Union to waste a certain performance.

MySQL Query two