List:General Discussion« Previous MessageNext Message »
From:Colin McKinnon Date:December 18 1999 2:30pm
Subject:Re: How to choose an index?
View as plain text  
At 13:35 18/11/99 +0100, Jochen Wiedmann wrote:
>I have a table similar to the following:
>
>	start INTEGER
>	end INTEGER
>
>where start <= end are arbitrary ranges.
>
>
>Does anyone have recommendations of how to choose an index
>that allows me to select those records that include a certain
>number? In other words, I like to optimize the query
>
>	SELECT * FROM mytable WHERE start <= mynumber AND
>		end >= mynumber;
>
>
>Thanks,
>
>Jochen
<snip>

This is a bit OT. but its so easy.....


Have 2 indices; one of each! - 
	ALTER TABLE tablename ADD INDEX tablename_se (start, end);
	ALTER TABLE tablename ADD INDEX tablename_es (end, start);

Then the optimizer can choose which is the best to use.

Colin


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