List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 1 2000 3:07am
Subject:Re: performance (indexing) question
View as plain text  
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
Thread
performance (indexing) questionThomas Kwan1 Oct
  • Re: performance (indexing) questionDan Nelson1 Oct
  • Re: performance (indexing) questionThomas Kwan1 Oct