I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing!
Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9)
Table has 3 million records
indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi)
------------------------
Here's the query:
select * from acg where zip4_lo_pot <= '801281111' and zip4_hi_pot >= '801281111'
------------------------
Explain shows:
type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where
This kind of query cannot be efficiently optimized on a pre-4.1 version. With 4.1, if you are using MyISAM tables you could make (zip4_lo_pot,zip4_hi_pot) a spatial column with a spatial index. See http://www.mysql.com/doc/en/Spatial_extensions_in_MySQL.html
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
