mysql explain select r2 query_id from rr_criteria as r1 rr_criteria as

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
mysql> explain select r2.query_id from rr_criteria as r1, rr_criteria as r2 where r1.query_id = 296 and r1.type_id = r2.type_id and r1.value = r2.value;
+----+-------------+-------+------+------------------------------------------+----------------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------------------+----------------------+---------+-----------------+------+-------------+
| 1 | SIMPLE | r1 | ref | rr_criteria_type_id,rr_criteria_query_id | rr_criteria_query_id | 4 | const | 1 | |
| 1 | SIMPLE | r2 | ref | rr_criteria_type_id | rr_criteria_type_id | 4 | site.r1.type_id | 265 | Using where |
+----+-------------+-------+------+------------------------------------------+----------------------+---------+-----------------+------+-------------+
2 rows in set (0.00 sec)
mysql> explain select query_id from rr_criteria where (type_id, value) in (select type_id, value from rr_criteria where query_id=296);
+----+--------------------+-------------+------+------------------------------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+------+------------------------------------------+----------------------+---------+-------+------+-------------+
| 1 | PRIMARY | rr_criteria | ALL | NULL | NULL | NULL | NULL | 318 | Using where |
| 2 | DEPENDENT SUBQUERY | rr_criteria | ref | rr_criteria_type_id,rr_criteria_query_id | rr_criteria_query_id | 4 | const | 1 | Using where |
+----+--------------------+-------------+------+------------------------------------------+----------------------+---------+-------+------+-------------+
2 rows in set (0.00 sec)