I am reposting this because it got sent right at the start of all the fun with the mailing list.
Best, Kyle --------------------- 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 -- Quicknet's MicroTelco fax and voice service has just added another carrier giving MicroTelco users more low rates to choose from. This new carrier is Altair Telecom - a low cost, worldwide voice telephony carrier that is available by up-grading at no additional cost to Internet SwitchBoard v6.0 or MicroTelco Gateway v2.5. http://www.quicknet.net/download/ Cut costs, Fax smart. Use iPrint2Fax worldwide and save! ========================================== FREE software download available at www.iPrint2Fax.com ========================================== --------------------------------------------------------------------- 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
