I have a series of data in one table that I need to put into a set of ranges.
Here is a simplified version of the tables:
data table fields (data):
val int(10) not null,
row_id int(10) not null auto_increment,
primary key
Range table fields (range):
lo int(10) not null,
hi int(10) not null,
range_id int(10) not null,
primary key (lo,hi),
index rng_indx(range_id)
What I need to do is to count the number of entries in the data table that
fall in the ranges in the range table. Ideally, I would have something
like this:
SELECT r.range_id as 'Range', count(*) as 'Hits'
FROM data d, range r
WHERE d.val BETWEEN r.lo AND r.hi
GROUP BY r.range_id
When I set up a test for this, "explain" shows that I will be doing a table
scan. This is not what I want. I have 5M rows in data and 10k+ in range.
The ranges do not overlap, so I will not get multiple rows per value in
the data table. Some values may lie outside all ranges.
Am I misusing "between" here? If so, is there a construct in MySQL that
I can use to speed this up? I know that comparison queries other than
equal and not equal tend to cause table scans, but between seems to
work with very different queries. Should I restructure the query somehow?
Any help from the guru's is appreciated.
Best,
Kyle
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
