MySQL Forums
Forum List  »  Optimizer & Parser

Rows Examined, why so many?
Posted by: matt roberts
Date: September 20, 2005 12:15PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
Rows Examined, why so many?
21313
September 20, 2005 12:15PM
9553
September 20, 2005 03:18PM
5914
September 21, 2005 08:12AM
4782
September 20, 2005 03:54PM
4347
September 20, 2005 07:10PM
4174
September 21, 2005 05:05AM
4088
September 21, 2005 08:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.