Handling mysql subqueries with the in keyword 1317 error _ MySQL-mysql tutorial

Processing mysql subqueries with the in keyword 1317 error bitsCN.com

1317 error in mysql subquery using the in keyword

Error 1317 mysql query execution interrupted message content: query execution is interrupted (the database is suspended directly)

1. symptom:

(1) using subquery statements in the PHP program causes Mysql to automatically "suspend", that is, the database is "stuck" and the program cannot run normally.

(2) execute the subquery statement on the mysql command line. Mysql needs to wait for a long time and prompts "Error 1317 mysql query execution interrupted"

2. there are two solutions:

The total number of records in the 006_kh table is 24256. The total number of records in the uzone_27020.kh table is 52327.

Original SQL statement (subquery ):

[Html]

SELECT count (kh_id) FROM '006 _ kh' WHERE kh_id in (select khbh from uzone_2701_kh where uzbh = '000000' and jgm = '000000 ')

Use the desc command for analysis. The result is as follows:

[Html]

Mysql>

Mysql> desc SELECT count (kh_id) FROM '006 _ kh' WHERE kh_id in (select khbh from uzone_2701_kh where uzbh = '000000' and jgm = '000000 ');

+ ---- + -------------------- + --------------- + ------- + ------------- + --------- + ------ + ------- + -------------------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + -------------------- + --------------- + ------- + ------------- + --------- + ------ + ------- + -------------------------- +

| 1 | PRIMARY | 006_kh | index | NULL | PRIMARY | 4 | NULL | 89394 | Using where; Using index |

| 2 | dependent subquery | uzone_2701_kh | ALL | NULL | 24256 | Using where |

+ ---- + -------------------- + --------------- + ------- + ------------- + --------- + ------ + ------- + -------------------------- +

2 rows in set (0.00 sec)

(1) Method 1:

SQL script

[Html]

Select count (kh_id) FROM '006 _ kh' where kh_id in (select khbh from uzone_2701_kh where uzbh = '000000' and jgm = '000000') as khid_array)

Use the desc command for analysis. The result is as follows:

[Html]

Mysql> desc select count (kh_id) FROM '006 _ kh' where kh_id in (select khbh from uzone_2701_kh where uzbh = '000000' and jgm = '000000 ') as khid_array );

+ ---- + -------------------- + --------------- + ------- + ------------- + --------- + ------ + ------- + -------------------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + -------------------- + --------------- + ------- + ------------- + --------- + ------ + ------- + -------------------------- +

| 1 | PRIMARY | 006_kh | index | NULL | PRIMARY | 4 | NULL | 96767 | Using where; Using index |

| 2 | dependent subquery | | ALL | NULL | 24099 | Using where |

| 3 | DERIVED | uzone_2701_kh | ALL | NULL | 24256 | Using where |

+ ---- + -------------------- + --------------- + ------- + ------------- + --------- + ------ + ------- + -------------------------- +

3 rows in set (0.02 sec)

(2) Method 2:

SQL script:

[Html]

Select count (a. kh_id) from 011_kh a inner join uzone_2701_kh B on a. kh_id = B. khbh where B. uzbh = '2016' and B. jgm = '2016'

Use the desc command for analysis. The result is as follows:

[Html]

Mysql>

Mysql> desc select count (. kh_id) from 011_kh a inner join uzone_2701_kh B on. kh_id = B. khbh where B. uzbh = '000000' and B. jgm = '000000 ';

+ ---- + ------------- + ------- + -------- + --------------- + --------- + -------------------- + ------- + ------------- +

| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------- + -------- + --------------- + --------- + -------------------- + ------- + ------------- +

| 1 | SIMPLE | B | ALL | NULL | 24256 | Using where |

| 1 | SIMPLE | a | eq_ref | PRIMARY | 4 | dxzs_v2_new. B .khbh | 1 | Using index |

+ ---- + ------------- + ------- + -------- + --------------- + --------- + -------------------- + ------- + ------------- +

2 rows in set (0.00 sec)

My test conclusion: the query using JOIN statements is not necessarily faster than that using subqueries. Based on my own test results and DESC analysis results, the JOIN statement is faster, high efficiency. Therefore, when the in keyword is used for subqueries, the second type is strongly recommended!

BitsCN.com