Re: mysql 'range' query optimization

[gerald_clark]

Johannes Ullrich wrote:



This problem is bugging me for a while now:

I have a table which has three columns:
'table':
start: long integer
end: long interg
property: varchar(200)
Now I am trying to retrieve the 'property' for a 'number':

select property from table where number between start and end

well, easy enough. Problem: It takes foreve, and according to
'explain', index are sometimes used and sometimes are not used
(for identical queries)
 

Show us. We can't see the query or the output of explain from here.

The table has about 700,000 rows. I have indexes on start, end and
(start,end). start and end are unique.
'end' is the primary key.
there are supposed to be no overlaps, but there are 
numbers that are not covered by any range.

'explain' sometimes shows the query using a key, sometimes it doesn't
:-(... all of this is kind of
'random'. The table is rather static (<100 updates / day)
Show us examples of both.  The queries must be different.

This problem has haunted me from 3.x days. Currently I am running 4.0.15

Different ways I did try to ask the query:

select property from table where end>number order by end asc limit 1

select property from table where number>start and number<end
 (this one sometimes works much faster if a 'limit 1' is added.
but only sometimes. :-/ )










 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]