In the last episode (Sep 30), Thomas Kwan said:
> I ran the following statement, and it took less than 1 second.
>
> mysql> SELECT * FROM catalog_part ORDER BY cp_d_created DESC LIMIT 0,1;
>
> Then, I ran the following statement, it took couple minutes
>
> mysql> SELECT * FROM catalog_part WHERE cp_state = 'OPEN' ORDER BY cp_d_created
> DESC LIMIT 0,1;
>
> I have indexes on cp_state, and cp_d_created. I think mysql use
> cp_state index when it ran the second statement, and there are a lot
(hint: you can be sure by running an EXPLAIN SELECT... )
> of entries with cp_state='OPEN' (more than 1 million entries). How
> can I instruct mysql to use the index on cp_d_created instead in my
> 2nd sql statement?
You probably don't want to. That would force mysql to walk the
cp_d_created index, then for each entry to look up that record to see
whether it had cp_state='OPEN'. If there were 50,000 records before
one with 'OPEN', that would be 50,000 random seeks. Even at 4ms/sec
that's over 3 minutes. If, on the other hand, you have an 'OPEN'
record early on, it'll be pretty quick.
You can test it by adding IGNORE INDEX(cp_state) to your SELECT. You
might also have to add USE INDEX (cp_d_created).
A better solution would be to create a multi-key index on
(cp_state,cp_d_created). Then mysql should be able to find the record
you're looking for instantly.
--
Dan Nelson
dnelson@stripped
