Howto increase performance???

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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.

4

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.

5

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

6

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.