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)
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)
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. :-/ )
--
--------------------------------------------------------------
Johannes Ullrich [EMAIL PROTECTED]
pgp key: http://johannes.homepc.org/PGPKEYS
--------------------------------------------------------------
"We regret to inform you that we do not enable any of the
security functions within the routers that we install."
[EMAIL PROTECTED]
--------------------------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
