I’m using a zip_employee table (33k records) defining per zip-code range (zipfrom, zipto) the employee who is responsible for this area, something like this:
CREATE TABLE zip_emp
( zipfrom char(10) NOT NULL, zipto char(10) NOT NULL, empid CHAR(10) NOT NULL
PRIMARY KEY (zipfrom,zipto)
);
When I’m executing a simple query like the one beneath, the performance is very, very slow:
select sta.statid, zip.empid
from statistics sta, zip_emp zip
where sta.zip between zip.zipfrom and zip.zipto
Can someone tell me why a query using a from-to join is so slow (a “normal” query is very fast) and what is the alternative?
What is the table structure of the statistics table, and how many records are in it? If I’m not mistaken, your query will do a cross join of the two tables (basically joining every record of zip_emp to every record of statistics), and then do a table scan through that set.
Ok, so the explain show that there is no index used by the request for the table statistics, if it’s possible juste create a new index on zip column or modify your primary key to include zip column.
Humm, the 2 indexes you created are on the wrong table, you need to create 1 index on “stat” table.
And the 2 new indexes (zipfrom,zipto) are not very good, you should drop them, and let the optimiser use the PRIMARY key.