Re: MySQL 5.1 does not seem to be using the index that I expect it to.

[Tom Muldoon]

I just happened to notice that the index used (and the actual key_len) is dependent upon the number of rows associated with the columns specified in the where clause.

Take, for example, the following query in which the recipient_id is 185 (64552 rows) and/or 128 (3789 rows)...

explain extended select
        activityev0_.id as id158_,
        activityev0_.trigger_time as trigger2_158_,
        activityev0_.note as note158_,
        activityev0_.type as type158_,
        activityev0_.status as status158_,
        activityev0_.notification_status as notifica6_158_,
        activityev0_.submission_id as submission7_158_,
        activityev0_.actor_id as actor8_158_,
        activityev0_.actor_display_name as actor9_158_,
        activityev0_.actor_user_type as actor10_158_,
        activityev0_.recipient_id as recipient11_158_,
        activityev0_.recipient_display_name as recipient12_158_,
        activityev0_.recipient_user_type as recipient13_158_,
        activityev0_.system_id as system14_158_,
        activityev0_.host_name as host15_158_ 
    from
        activity_event activityev0_
    where
        activityev0_.recipient_id=?
    order by
        activityev0_.trigger_time desc,
        activityev0_.id desc

The explain plan for recipient_id 185 (64552 rows) uses the expected index, recipient_id__trigger_time__id, but it's key_len is just 8 (indicating that it is not using the trigger_time or id, right?)...

'1', 'SIMPLE', 'activityev0_', 'ref', 'recipient_id__status__trigger_time__id,recipient_id__trigger_time__id', 'recipient_id__trigger_time__id', '8', 'const', '61600', '100.00', 'Using where'

The explain plan for recipient_id 128 (3789 rows) uses the unexpected index, recipient_id__status__trigger_time__id, but it's key_len is also just 8...

'1', 'SIMPLE', 'activityev0_', 'ref', 'recipient_id__status__trigger_time__id,recipient_id__trigger_time__id', 'recipient_id__status__trigger_time__id', '8', 'const', '5160', '100.00', 'Using where; Using filesort'

That said, the following questions arise...

- Why is the key_len equal to 8 in both cases (indicating that the optimizier is using only the recipient_id part (of the multi-part index)? Why wouldn't the optimizier use the trigger_time and the id (in addition to the recipient_id)?

- Why does the second query depend upon a filesort? And why does it use the unexpected index, recipient_id__status__trigger_time__id, (since status is not specified as a condition to the query)?

- Why does the explain plan for the second query indicate that MySQL must examine 5160 rows when there are in fact only 3789 rows that match? Is this due to the fact that for InnoDB tables, the number is just an estimate (and may not be exact)?