Rows read over 2 million but EXPLAIN says it should be 65.

[James Callaghan]

Hi there, I've been looking around for a solution to this for a while and can't get past basic use-explain-and-add-an-index type solutions.

I've got a query:

select count(bulk.id) from logmt, bulk, notifications where logmt.current_notification_id=notifications.id and bulk.third_party_identifier=logmt.identifier and bulk.sent_to_queue=1 and bulk.client_name=logmt.client_name and bulk.bulk_batch_id = 2842 and notification.status = 'DELIVERED';

It popping up in the slow query log with the following details:
# Query_time: 16 Lock_time: 0 Rows_sent: 1 Rows_examined: 2657655

Yes, TWO MILLION ROWS EXAMINED!!

So I do an EXPLAIN on the query to see whats happening and I get the following results:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bulksmsvo1_
type: ref
possible_keys: third_party_identifier,bulk_sms_batch_id
key: bulk_sms_batch_id
key_len: 5
ref: const
rows: 65
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: smsmtvo0_
type: ref
possible_keys: client_name,identifier
key: client_name
key_len: 13
ref: platform1.bulksmsvo1_.client_name
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: smsmtnotif2_
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: platform1.smsmtvo0_.current_notification_id
rows: 1
Extra: Using where

So, explain is telling me that in the three joins I'm examining 65, 1 and 1 rows. If I run the query though, it still takes 15 seconds and shows up in the slow query log as examining over two million.

The bulk table has 128K rows, the logmt table has 114K rows and the notifications table has 196K rows. the two million number is not coming from total row count in one of these.

I'm at a loss of what to do next, any pointers would be much appreciated.