List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 14 2000 3:21pm
Subject:How to choose an index?
View as plain text  
Hi again!

I was scanning through some old mails to check if we had some unsolved
things before doing a new relase and stumbled across the following mail:

>>>>> "Jochen" == Jochen Wiedmann <joe@stripped> writes:

Jochen> Hi,

Jochen> I have a table similar to the following:

Jochen> 	start INTEGER
Jochen> 	end INTEGER

Jochen> where start <= end are arbitrary ranges.


Jochen> Does anyone have recommendations of how to choose an index
Jochen> that allows me to select those records that include a certain
Jochen> number? In other words, I like to optimize the query

Jochen> 	SELECT * FROM mytable WHERE start <= mynumber AND
Jochen> 		end >= mynumber;

The only 'easy' thing I can think of is if you know how big the biggest
start-end range is.  In this case you can do:

SELECT * FROM mytable WHERE start between my_number-max_range and mynumber AND
end between mynumber and my_number+max_range;

This will let MySQL use a much smaller set of rows to examine for the
range.

Regards,
Monty

PS: Sorry for the delay, hope late was better than never.
Thread
How to choose an index?Jochen Wiedmann18 Nov
  • Re: How to choose an index?Colin McKinnon18 Nov
  • How to choose an index?Michael Widenius14 Jan