Optimization tips for slow subquery efficiency of mysql in statements, mysql examples

Optimization tips for slow subquery efficiency of mysql in statements, mysql examples

The table structure is as follows. There are only 690 articles.

Article table article (id, title, content) label table tag (tid, tag_name) label intermediate table article_tag (id, tag_id, article_id)

The tid of a tag is 135, and the list of articles whose tid is 135 is queried.

In the first article, we used the following statements to query the query, which was very slow:

select id,title from article where id in(select article_id from article_tag where tag_id=135)

The speed is fast:

select article_id from article_tag where tag_id=135

The query result is five articles with the id 428,429,430,431,432

Using the following SQL to check the article is also fast:

select id,title from article where id in(428,429,430,431,432)

Solution:

select id,title from article where id in(select article_id from (select article_id from article_tag where tag_id=135) as tbt)

Other solutions: (example)

mysql> select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

The output content is omitted to save space.

67 rows in set (12.00 sec)

It took only 67 rows of data to return, but it took 12 seconds. There may be many such queries in the system at the same time, and the system will surely be unable to handle them. Use desc to check the description (Note: The description can also be used)

mysql> desc select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+| 1 | PRIMARY | abc_number_prop | ALL | NULL | NULL | NULL | NULL | 2679838 | Using where || 2 | DEPENDENT SUBQUERY | abc_number_phone | eq_ref | phone,number_id | phone | 70 | const,func | 1 | Using where; Using index |+----+--------------------+------------------+--------+-----------------+-------+---------+------------+---------+--------------------------+2 rows in set (0.00 sec)

We can see that more than 2 million rows are scanned when this query is executed. Isn't the index created? Let's take a look.

mysql>show index from abc_number_phone;+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| abc_number_phone | 0 | PRIMARY | 1 | number_phone_id | A | 36879 | NULL | NULL | | BTREE | | || abc_number_phone | 0 | phone | 1 | phone | A | 36879 | NULL | NULL | | BTREE | | || abc_number_phone | 0 | phone | 2 | number_id | A | 36879 | NULL | NULL | | BTREE | | || abc_number_phone | 1 | number_id | 1 | number_id | A | 36879 | NULL | NULL | | BTREE | | || abc_number_phone | 1 | created_by | 1 | created_by | A | 36879 | NULL | NULL | | BTREE | | || abc_number_phone | 1 | modified_by | 1 | modified_by | A | 36879 | NULL | NULL | YES | BTREE | | |+------------------+------------+-------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+6 rows in set (0.06 sec)mysql>show index from abc_number_prop;+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| abc_number_prop | 0 | PRIMARY | 1 | number_prop_id | A | 311268 | NULL | NULL | | BTREE | | || abc_number_prop | 1 | number_id | 1 | number_id | A | 311268 | NULL | NULL | | BTREE | | || abc_number_prop | 1 | created_by | 1 | created_by | A | 311268 | NULL | NULL | | BTREE | | || abc_number_prop | 1 | modified_by | 1 | modified_by | A | 311268 | NULL | NULL | YES | BTREE | | |+-----------------+------------+-------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.15 sec)

From the above output, we can see that the two tables have created indexes on the number_id field.
Check whether the subquery has any problems.

mysql> desc select number_id from abc_number_phone where phone = '82306839';+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+| 1 | SIMPLE | abc_number_phone | ref | phone | phone | 66 | const | 6 | Using where; Using index |+----+-------------+------------------+------+---------------+-------+---------+-------+------+--------------------------+1 row in set (0.00 sec)

No problem. You only need to scan several rows of data. The index takes effect.

Query it to see:

mysql> select number_id from abc_number_phone where phone = '82306839';+-----------+| number_id |+-----------+| 8585 || 10720 || 148644 || 151307 || 170691 || 221897 |+-----------+6 rows in set (0.00 sec)

Directly store the data obtained from the subquery in the preceding query.

mysql> select * from abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897);67 rows in set (0.03 sec)

It seems that MySQL is not good enough to process subqueries. I tried both MySQL 5.1.42 and MySQL 5.5.19.

I searched the network and found that many people have encountered this problem:

Reference 1: Using join instead of subquery for MySQL Optimization

Reference 2: MYSQL subquery and nested query optimization instance Parsing

Based on the suggestions of these materials on the Internet, use join instead.
Before modification:

select * from abc_number_prop where number_id in (select number_id from abc_number_phone where phone = '82306839');

After modification:

select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';mysql> select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';67 rows in set (0.00 sec)

Good results. The query time is almost 0. Let's see how MySQL executes this query.

mysql>desc select a.* from abc_number_prop a inner join abc_number_phone b on a.number_id = b.number_id where phone = '82306839';+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+| 1 | SIMPLE | b | ref | phone,number_id | phone | 66 | const | 6 | Using where; Using index || 1 | SIMPLE | a | ref | number_id | number_id | 4 | eap.b.number_id | 3 | |+----+-------------+-------+------+-----------------+-----------+---------+-----------------+------+--------------------------+2 rows in set (0.00 sec)

Conclusion: When the subquery speed is slow, you can use JOIN to rewrite the subquery for optimization.

There are also articles on the Internet that queries using JOIN statements are not necessarily faster than statements using subqueries.

The mysql Manual also mentioned that the specific original article is in this chapter of the mysql document:
I .3. Restrictions on Subqueries
13.2.8. Subquery Syntax

Excerpt:

1) subqueries using IN:

Subquery optimization for IN is not as valid tive as for the = operator or for IN (value_list) constructs.

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

SELECT... FROM t1 WHERE t1.a IN (SELECT B FROM t2 );

The optimizer rewrites the statement to a correlated subquery:

SELECT... FROM t1 where exists (SELECT 1 FROM t2 WHERE t2. B = t1.a );

If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O (M × N), rather than O (M + N) as it wocould be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than query written using an IN (value_list) construct that lists the same values that the subquery wocould return.

2) about converting subqueries into join:

The optimizer is more mature for joins than for subqueries, so in your cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

An exception occurs for the case where an IN subquery can be rewritten as a select distinct join. Example:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition );

That statement can be rewritten as follows:

Select distinct col FROM t1, t2 WHERE t1.id _ col = t2.id _ col AND condition;

But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery

Summary

The above is all the content of this article on the optimization tips for mysql in statement subquery efficiency is slow. If you are interested, refer: let's talk about the efficiency of mysql subquery union and in, and the introduction of MySQL Optimization in enterprise production. If you have any questions, please leave a message.

I hope this article will help you.