Howto increase performance???

Hello,

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?

Thanks,

Menrone

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.

Hello dsuehrin,

the structure of the the statistics table is:

CREATE TABLE IF NOT EXISTS statistics (
id int(11) NOT NULL auto_increment,
zip char(10) NOT NULL default ‘’,
paperid char(10) NOT NULL default ‘’,
freqid char(10) NOT NULL default ‘’,
mdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY MDATE (mdate)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=39979 ;

At the moment it has 12K records.

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.

After creating two seperate indexes on zipfrom and zipto the explain plan returns:

1 SIMPLE sta ALL NULL NULL NULL NULL 13074
1 SIMPLE zip ALL zipfrom,zipto NULL NULL NULL 38065 Using where

But still the performance is hopeless

Thanks

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.