problem using index
I have a table with user data and a table with (Dutch) zipcodes
All zipcodes start with 4 numbers followed by a space and then 2 letters (for example: '1234 AZ')
The table with zipcodes contains (numerical) ranges of zipcodes that represent a certain area in holland.
What i want to do is JOIN this information on the user-date
A dump of the table:
CREATE TABLE postcodes (
postcode_id tinyint(3) unsigned NOT NULL auto_increment,
range_from smallint(5) unsigned default NULL,
range_till smallint(5) unsigned default NULL,
regio tinyint(3) unsigned default NULL,
PRIMARY KEY (postcode_id),
UNIQUE KEY range (range_from,range_till),
UNIQUE KEY postcode_id (postcode_id),
KEY rf (range_from),
KEY rt (range_till),
KEY regio (regio)
) TYPE=MyISAM;
if i do this query:
explain SELECT
dm.id, SUBSTRING(dm.postcode, 1, 4) AS range
FROM data_main AS dm
LEFT JOIN postcodes AS p ON SUBSTRING(dm.postcode, 1, 4) BETWEEN p.range_from AND p.range_till
ORDER BY id DESC
LIMIT 0, 10
mysql tells me this:
table,type,possible_keys,key,key_len,ref,rows,Extra
dm,index,NULL,PRIMARY,8,NULL,13942,Using temporary
p,index,NULL,range,6,NULL,164,Using index
so, despite the index, it still views all 164 records of the postcodes table.
No matter what i try, i can't get it to view only 1 (all ranges are unique)
Anyone an idea how to solve this?
All help is much appreciated