Rows Examined, why so many?
Why is Rows_examined 1959?
# Query_time: 2 Lock_time: 0 Rows_sent: 500 Rows_examined: 1959
SELECT mb.em_id, em_seen, em_subject, mb.em_create, em_format, em_from, em_to, pr_uname, uname FROM mail_mbox mb INNER JOIN mail m ON m.em_id=mb.em_id LEFT JOIN profile ON pr_uname=em_from LEFT JOIN members ON uname=em_uname WHERE em_uname='brother' AND em_mbox='INBOX' ORDER BY mb.em_uname, mb.em_mbox, mb.reverse_create;
mysql> explain SELECT mb.em_id, em_seen, em_subject, mb.em_create, em_format, em_from, em_to, pr_uname, uname FROM mail_mbox mb INNER JOIN mail m ON m.em_id=mb.em_id LEFT JOIN profile ON pr_uname=em_from LEFT JOIN members ON uname=em_uname WHERE em_uname= 'brother' AND em_mbox='INBOX' ORDER BY mb.em_uname, mb.em_mbox, mb.reverse_create;
+----+-------------+---------+--------+--------------------------------+------------------+---------+----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+--------+--------------------------------+------------------+---------+----------------------------+------+-------------+
| 1 | SIMPLE | mb | ref | PRIMARY,ix_reverse_crete,em_id | ix_reverse_crete | 121 | const,const | 519 | Using where |
| 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 4 | mail_db.mb.em_id | 1 | |
| 1 | SIMPLE | profile | eq_ref | PRIMARY | PRIMARY | 120 | mail_db.m.em_from | 1 | Using index |
| 1 | SIMPLE | members | ref | ix_uname,uname,uname_2 | uname_2 | 120 | mail_db.mb.em_uname | 1 | Using index |
+----+-------------+---------+--------+--------------------------------+------------------+---------+----------------------------+------+-------------+
mysql> select count(*) from mail_mbox where em_uname='brother' and em_mbox='INBOX';
+----------+
| count(*) |
+----------+
| 500 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from mail join mail_mbox on mail.em_id=mail_mbox.em_id where mail_mbox.em_uname='brother' and mail_mbox.em_mbox='INBOX';
+----------+
| count(*) |
+----------+
| 500 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from profile where pr_uname='brother';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from members where uname='brother';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
I am wondering why it examined 1959 rows, where do they come from? Everything is using an index. It first reads using ix_reverse_crete where is an index on (uname, mailbox, reverse_create). For each row returned, there should be one row in mail. The Primay key for mail is "mail.em_id", and the join shows that there is indeed a one to one relation. pr_uname is the primary key of profile, so it's unique too. members.uname is a unique index on "uname". To me, the EXPLAIN shows that there should be 519*1*1*1 rows examined. Because this is an active email database, it's normal for the statistics to be a little bit off, here by 19 rows, since 500 where returned. But 1959 rows examined? I don't get it. This query should be lighting fast since it only uses primary keys and unique indexes, but yet it makes it to the slow query often. The database not that big:
mysql> select count(*) from mail; select count(*) from mail_mbox; select count(*) from members; select count(*) from profile;
+----------+
| count(*) |
+----------+
| 269499 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 331198 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 24378 |
+----------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 18913 |
+----------+
1 row in set (0.00 sec)
I thought maybe it was the ORDER BY doing some SORT, but the index looks ordered and there is no mention of any sorting in the explain.
Any idea?