From: Dan Nelson
Date: July 24 2002 9:58pm
Subject: Re: Query Optimization
List-Archive: http://lists.mysql.com/mysql/115475
Message-Id: <20020724215816.GI13851@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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)
> ) 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'

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
	dnelson@stripped