[thelist] MySQL Performance Question

Jay Blanchard jay.blanchard at niicommunications.com
Tue Apr 30 12:33:01 CDT 2002


Howdy all,

We have a curious situation here, and I was wondering if any of you have
encountered this before.

We have MySQL installed on a FreeBSD machine (733mHz, 256Mb RAM). Compiled
from source.
We installed MySQL on another FreeBSD machine (dual 866mHz, 512Mb RAM).
Compiled from source.

The reason we went to the new machine is that we have a massive database
that for practical purposes needed to live by itself. We moved this database
from the first machine to the second machine.

Most of the queries run slightly faster on the new machine, but one query in
particular takes twice the time to run on the new machine. The query ;

select	c10.RecordID, count(*) as Quantity, sum(c10.Minutes) as Minutes
from		tblClass10 c10 left outer join tblANI a
on		c10.FromNumber = a.ExemptPhone
where		c10.RecordID = '100101'
and		a.ExemptPhone is null
group by	c10.RecordID;

On the first machine runs in approximately 20 minutes, on the second machine
runs in about 40-45 minutes. tblClass10 has 5.5 million records, tblANI has
3100 records. Out of tblClass10, the records matching the RecordID criteria
'100101' is approximately 170,000 (today).

We are thinking that it has something to do with the flags that were used to
compile MySQL with reguards to dual processor machines. I am also up for any
query optimization that would work too. I have tried INSERT ...SELECT the
100101 records into a new table and then running the query above (replacing
tblClass10 with the new table), with the results being no faster.

TFAYHIA!

Jay





More information about the thelist mailing list