List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 24 2002 9:58pm
Subject:Re: Query Optimization
View as plain text  
In the last episode (Jul 24), Dave Dutcher said:
> 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'

Put an index on (begin,end).  It will only have to do a range scan on
that index to find the matching records.  You might also want to make
those columns integers instead of chars; they'll take up a bit less
space that way.

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