MySQL in subquery efficiency slow optimization

Now CMS system, blog system, BBS and so like to use tag tag as a cross-link, so I also use the next. But after using it found that I want to query a tag list of articles when the speed is very slow, up to 5 seconds! Baisibuxie (later finally solved), my table structure is the following, the article only 690 articles.

Article table article (id,title,content)
Tag table tag (tid,tag_name)
Label Article intermediate table Article_tag (id,tag_id,article_id)
There's a label for the TID is 135, I'll check the label Tid is 135 of the article List
With the following statement, I found that the speed is very slow, my article only 690 articles
Select Id,title from article where ID in (
Select article_id from Article_tag where tag_id=135
)
This speed is fast: Select article_id from Article_tag where tag_id=135
The query results are five articles with an ID of 428,429,430,431,432
I use the following SQL to read the article in the form of death.
Select Id,title from article where ID in (
428,429,430,431,432
)
I do not seem to be so slow in SQL Server, I do not know how to write a good MySQL, I can not think of where the slow.

Then I found a 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 workarounds: (for example)

Mysql> SELECT * from Abc_number_prop where number_id in (select number_id from abc_number_phone where phone = ' 8230 6839 ');

In order to save space, omit the output content, the same below.

All in Set (12.00 sec)

Only 67 rows of data returned, but it took 12 seconds, and the system may have a lot of such queries, the system must not be able to carry. Take a look at Desc (note: Explain can also)

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)

As can be seen from the above information, when executing this query will be scanned more than 2 million rows, is not created index it, look at

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)

As you can see from the above output, these two tables were indexed on the number_id field.

See if there is a problem with the subquery itself.

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, just scan a few rows of data and the index works. Check it out.

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)

The data directly from the query is placed in the query above

Mysql> SELECT * from Abc_number_prop where number_id in (8585, 10720, 148644, 151307, 170691, 221897);

All in Set (0.03 sec)

The speed is also fast, it seems that MySQL is not good enough to deal with the sub-query. I've tried both MySQL 5.1.42 and MySQL 5.5.19, both of which have this problem.

Search the network, found that many people have encountered this problem:

Reference 1: Using connections (join) in place of subqueries (sub-queries) MySQL optimization series records
http://blog.csdn.net/hongsejiaozhu/article/details/1876181
Reference 2: Web Development Diary-mysql subqueries and nested query optimizations
http://dodomail.iteye.com/blog/250199

Based on the recommendations on the Internet, use join to try it out.

Before modification: SELECT * from Abc_number_prop where number_id in (select number_id from abc_number_phone where phone = ' 82306839 ');

Modified: Select A.* from Abc_number_prop a inner joins Abc_number_phone b on a.number_id = b.number_id where phone = ' 82306839 ';

Mysql> Select a.* from Abc_number_prop a inner joins Abc_number_phone b on a.number_id = b.number_id where phone = ' 82306839 ';

All in Set (0.00 sec)

The effect is good, the query time spent almost 0. Take a look at how MySQL executes this query.

Mysql>desc Select a.* from Abc_number_prop a inner joins 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)

Summary: When a subquery is slow, a join can be used to rewrite the query for optimization.

There are also articles on the web saying that queries that use JOIN statements are not always faster than statements that use subqueries.

Reference 3: Changing the view of MySQL sub-query
Http://hi.baidu.com/yzx110/blog/item/e694f536f92075360b55a92b.html

The MySQL manual also mentions the specific text in this section of the MySQL documentation:

I.3. Restrictions on subqueries

13.2.8. Subquery Syntax

Extract:

1) about subqueries using in:

Subquery optimization for are not as effective as for the = operator or for in (value_list) constructs.

A typical case for poor in subquery performance was when the subquery returns a small number of rows but the outer query re Turns a large number of rows to being compared to the subquery result.

The problem is this, for a statement this 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 Orde R of O (mxn), rather than O (m+n) as it would is for a uncorrelated subquery.

An implication was that's in subquery can be much slower than a query written using an in (value_list) construct t Hat lists the same values that the subquery would return.

2) about converting a query into a join:

The optimizer is more mature for joins than for subqueries, so on many cases a statement that uses a subquery can be EXECU Ted 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:

condition);

That statement can rewritten as follows:

condition;

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

From:http://www.cnblogs.com/xh831213/archive/2012/05/09/2491272.html