List:General Discussion« Previous MessageNext Message »
From:Dave Dutcher Date:July 24 2002 7:35pm
Subject:RE: Query Optimization
View as plain text  
Hi, I did some more research on my problem.  (Sorry for not doing it before
I posted the other message), and I think perhaps my question could be
summarized into the following.

Can I use an index to speed up a select max() or select min()?

I read what the manual has to say about a query like this:
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

However I want to do this:
SELECT MAX(key_part1) FROM table_name where key_part1 < 10

also what I want to do is basically equivalent to this:

SELECT key_part1 FROM table_name WHERE key_part1 < 10 ORDER BY key_part1
DESC LIMIT 1

In testing the second statement seems slower than the max() for some reason
though.

Is there anyway to get close to a O(logN) search time on these queries?

Thanks again,

Dave


-----Original Message-----
From: Dave Dutcher [mailto:djdutcher74@stripped]
Sent: Wednesday, July 24, 2002 1:04 PM
To: mysql@stripped
Subject: Query Optimization


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)
) TYPE=MyISAM;

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
records.

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

Thanks in advance,

Dave



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