Page MenuHomePhabricator
Paste P3215

(An Untitled Masterwork)
ActivePublic

Authored by SBisson on Jun 7 2016, 1:39 PM.
+----+--------------------+-------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | echo_notification | ALL | NULL | NULL | NULL | NULL | 563 | Using where |
| 2 | DEPENDENT SUBQUERY | echo_notification | ALL | NULL | NULL | NULL | NULL | 563 | Using where |
+----+--------------------+-------------------+------+---------------+------+---------+------+------+-------------+

Event Timeline

SBisson created this paste.Jun 7 2016, 1:39 PM

explain select
notification_user,
notification_event,
notification_bundle_display_hash,
notification_read_timestamp
from
echo_notification
where notification_bundle_base=1
and notification_read_timestamp is not null
and notification_bundle_display_hash in (

select notification_bundle_display_hash from echo_notification where notification_bundle_base=0 and notification_read_timestamp is null
);

Catrope added a subscriber: Catrope.Jun 7 2016, 1:40 PM
+------+--------------+-------------------+--------+---------------+--------------+---------+------+----------+-------------+
| id   | select_type  | table             | type   | possible_keys | key          | key_len | ref  | rows     | Extra       |
+------+--------------+-------------------+--------+---------------+--------------+---------+------+----------+-------------+
|    1 | PRIMARY      | echo_notification | ALL    | NULL          | NULL         | NULL    | NULL | 17516506 | Using where |
|    1 | PRIMARY      | <subquery2>       | eq_ref | distinct_key  | distinct_key | 35      | func |        1 |             |
|    2 | MATERIALIZED | echo_notification | ALL    | NULL          | NULL         | NULL    | NULL | 17516506 | Using where |
+------+--------------+-------------------+--------+---------------+--------------+---------+------+----------+-------------+
3 rows in set (0.00 sec)
mysql:research@x1-analytics-slave [enwiki]> explain select  notification_user,  notification_event,  notification_bundle_display_hash,  notification_read_timestamp  from  echo_notification  where notification_bundle_base=1  and notification_read_timestamp is not null  and notification_bundle_display_hash in (  select notification_bundle_display_hash from echo_notification where notification_bundle_base=0 and notification_read_timestamp is null ) order by notification_user, notification_event limit 300;
+------+--------------+-------------------+--------+---------------+--------------+---------+------+----------+-------------+
| id   | select_type  | table             | type   | possible_keys | key          | key_len | ref  | rows     | Extra       |
+------+--------------+-------------------+--------+---------------+--------------+---------+------+----------+-------------+
|    1 | PRIMARY      | echo_notification | index  | NULL          | user_event   | 8       | NULL |        1 | Using where |
|    1 | PRIMARY      | <subquery2>       | eq_ref | distinct_key  | distinct_key | 35      | func |        1 |             |
|    2 | MATERIALIZED | echo_notification | ALL    | NULL          | NULL         | NULL    | NULL | 17516512 | Using where |
+------+--------------+-------------------+--------+---------------+--------------+---------+------+----------+-------------+
3 rows in set (0.00 sec)

select notification_bundle_display_hash, count(distinct(notification_read_timestamp)) as ts from echo_notification where notification_bundle_display_hash <> '' and notification_bundle_display_hash in ( select notification_bundle_display_hash from echo_notification where notification_read_timestamp is not null ) group by notification_bundle_display_hash having ts <> 1;