List:General Discussion« Previous MessageNext Message »
From:Dave Dutcher Date:July 24 2002 6:03pm
Subject:Query Optimization
View as plain text  
I was wondering if somebody could give me any suggestions on how to optimize
a query I am working on.

This is my table:

CREATE TABLE testdata (
  Begin char(9) NOT NULL default '',
  End char(9) NOT NULL default '',
  UNIQUE KEY BeginEndIndex (Begin,End)

It is a table of ranges.  i.e. 1-5, 7-11, 20000-24000.  None of the ranges
overlap, and I'm trying to write a query to find a range that contains a
specific number such as 500,000.  So this is what I've written for a query:

select Begin, End
from testdata
where begin <= '005000000' and end >= '005000000'

On a table with 100,000 records the explain command tells me it is using the
BeginEndIndex, it says the key length is 9, and that it has to look through
about 27,000 records.  I would like to be able to configure the query or
indexes , so it will only have to look through a couple records if possible.

MySQL is super fast at a query like this:
select Begin from testdata where Begin = '005000000';

So I had the idea of trying this:
select max(Begin) from testdata where Begin <= '005000000';

I was hoping that it would do the same as the simple select, and then
because it has a sorted index it wouldn't have to search the previous
records to find the max, but explain still puts this at about 27,000

Has anyone else tried writing a query similar to this?  Does anybody have
any suggestions?

Thanks in advance,


Query OptimizationDave Dutcher24 Jul
  • RE: Query OptimizationDave Dutcher24 Jul
  • Re: Query OptimizationDan Nelson24 Jul