Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1, 10.2.15, 10.2, 10.3
-
None
-
Linux
Description
SELECT
|
*
|
FROM
|
`matched_activities` AS `ma_main` |
WHERE
|
`created_at` < "2018-05-22 18:40:23.700872" AND `created_at` > "2018-05-22 16:50:23.700957" AND NOT EXISTS( |
SELECT |
1
|
FROM |
`searches`
|
WHERE |
(
|
searches.id = ma_main.search1_id OR searches.id = ma_main.search2_id |
) AND `deleted_at` IS NOT NULL |
) AND NOT EXISTS( |
SELECT |
1
|
FROM |
`searches` AS `searches_outer` |
WHERE |
searches_outer.id = ma_main.search1_id AND EXISTS( |
SELECT |
1
|
FROM |
`searches` AS `searches_inner` |
WHERE |
searches_inner.user_id = searches_outer.user_id AND EXISTS( |
SELECT |
1
|
FROM |
`matched_activities` AS `ma_same_user` |
WHERE |
(
|
ma_same_user.search1_id = searches_inner.id AND `ma_same_user`.`user1_last_notification_at` > "2018-05-21 18:50:23" |
) OR( |
ma_same_user.search2_id = searches_inner.id AND `ma_same_user`.`user2_last_notification_at` > "2018-05-21 18:50:23" |
)
|
)
|
)
|
)
|
MariaDB-10.2.15
Empty set (54.12 sec)
|
 |
(root:localhost) [test]> explain SELECT * FROM `matched_activities` AS `ma_main` WHERE `created_at` < "2018-05-22 18:40:23.700872" AND `created_at` > "2018-05-22 16:50:23.700957" AND NOT EXISTS( SELECT 1 FROM `searches` WHERE ( searches.id = ma_main.search1_id OR searches.id = ma_main.search2_id ) AND `deleted_at` IS NOT NULL ) AND NOT EXISTS( SELECT 1 FROM `searches` AS `searches_outer` WHERE searches_outer.id = ma_main.search1_id AND EXISTS( SELECT 1 FROM `searches` AS `searches_inner` WHERE searches_inner.user_id = searches_outer.user_id AND EXISTS( SELECT 1 FROM `matched_activities` AS `ma_same_user` WHERE ( ma_same_user.search1_id = searches_inner.id AND `ma_same_user`.`user1_last_notification_at` > "2018-05-21 18:50:23" ) OR( ma_same_user.search2_id = searches_inner.id AND `ma_same_user`.`user2_last_notification_at` > "2018-05-21 18:50:23" ) ) ) );
|
+------+--------------------+----------------+--------+---------------------------------------------------------------------------------------+--------------------------+---------+------+-------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------------+----------------+--------+---------------------------------------------------------------------------------------+--------------------------+---------+------+-------+--------------------------+
|
| 1 | PRIMARY | ma_main | ALL | NULL | NULL | NULL | NULL | 23383 | Using where |
|
| 3 | MATERIALIZED | searches_outer | index | PRIMARY,searches_user_id_foreign | searches_user_id_foreign | 4 | NULL | 8496 | Using index |
|
| 3 | MATERIALIZED | <subquery4> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
|
| 4 | MATERIALIZED | searches_inner | index | searches_user_id_foreign | searches_user_id_foreign | 4 | NULL | 8496 | Using where; Using index |
|
| 5 | DEPENDENT SUBQUERY | ma_same_user | ALL | matched_activities_search1_id_search2_id_unique,matched_activities_search2_id_foreign | NULL | NULL | NULL | 23383 | Using where |
|
| 2 | DEPENDENT SUBQUERY | searches | ALL | PRIMARY | NULL | NULL | NULL | 8496 | Using where |
|
+------+--------------------+----------------+--------+---------------------------------------------------------------------------------------+--------------------------+---------+------+-------+--------------------------+
|
6 rows in set (0.00 sec)
|
MySQL-5.6
Empty set (0.64 sec)
|
 |
(root:localhost) [test]> explain SELECT * FROM `matched_activities` AS `ma_main` WHERE `created_at` < "2018-05-22 18:40:23.700872" AND `created_at` > "2018-05-22 16:50:23.700957" AND NOT EXISTS( SELECT 1 FROM `searches` WHERE ( searches.id = ma_main.search1_id OR searches.id = ma_main.search2_id ) AND `deleted_at` IS NOT NULL ) AND NOT EXISTS( SELECT 1 FROM `searches` AS `searches_outer` WHERE searches_outer.id = ma_main.search1_id AND EXISTS( SELECT 1 FROM `searches` AS `searches_inner` WHERE searches_inner.user_id = searches_outer.user_id AND EXISTS( SELECT 1 FROM `matched_activities` AS `ma_same_user` WHERE ( ma_same_user.search1_id = searches_inner.id AND `ma_same_user`.`user1_last_notification_at` > "2018-05-21 18:50:23" ) OR( ma_same_user.search2_id = searches_inner.id AND `ma_same_user`.`user2_last_notification_at` > "2018-05-21 18:50:23" ) ) ) );
|
+----+--------------------+----------------+--------+---------------------------------------------------------------------------------------+--------------------------+---------+-----------------------------+-------+------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+--------------------+----------------+--------+---------------------------------------------------------------------------------------+--------------------------+---------+-----------------------------+-------+------------------------------------------------+
|
| 1 | PRIMARY | ma_main | ALL | NULL | NULL | NULL | NULL | 22796 | Using where |
|
| 3 | DEPENDENT SUBQUERY | searches_outer | eq_ref | PRIMARY | PRIMARY | 4 | test.ma_main.search1_id | 1 | Using where |
|
| 4 | DEPENDENT SUBQUERY | searches_inner | ref | searches_user_id_foreign | searches_user_id_foreign | 4 | test.searches_outer.user_id | 9 | Using where; Using index |
|
| 5 | DEPENDENT SUBQUERY | ma_same_user | ALL | matched_activities_search1_id_search2_id_unique,matched_activities_search2_id_foreign | NULL | NULL | NULL | 22796 | Range checked for each record (index map: 0x6) |
|
| 2 | DEPENDENT SUBQUERY | searches | ALL | PRIMARY | NULL | NULL | NULL | 8496 | Range checked for each record (index map: 0x1) |
|
+----+--------------------+----------------+--------+---------------------------------------------------------------------------------------+--------------------------+---------+-----------------------------+-------+------------------------------------------------+
|
5 rows in set (0.00 sec)
|