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:[EMAIL PROTECTED]] Sent: Wednesday, July 24, 2002 1:04 PM To: [EMAIL PROTECTED] 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 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
