Strange behaviour on RH4.0 with MySQL 5.037

Hi All,

We have some strange results on a Redhat 4.0 server.

When executing a query, the server directly going to ‘copying to tmp table’ and the server is finished after 1-2 minutes.

Now,
Same software, same db, same my.cnf on a Windows OR FreeBSD server, the ‘copying to tmp table’ does not occur.
Query time 8-10 seconds! both FreeBSD and Windows servers are older machines with less ram Our linux machine has 4 gig.

Ok.
Analysing the query and DB.
OOPS: missing some indexes. Adding them:

Cool!! our linux machine is now finished in 8 seconds. (copying to tmp table shows up for only 2-3 seconds now)
BUT
Windows and FreeBSD now needs more time than before!!!
(18-20 seconds)

This is complete weird.
Does the mysql optimizer do some strange things under redhat?

Don’t know where to start looking now.
Anyone?

linux machine: dell 2950 2x dualcore xeon 4gig mem
Freebsd: dell dualcore 1,6 2 gig mem
Windows: Amd 1800 512mb

ALL myisam
WHY does the ‘copying to tmp table’ not occur on the other machines.

Thanks,
Olaf

You should have posted original EXPLAIN for query on different boxes and table structure

chances are there are difference but you just have not spotted them.

After you added the index - it could be just system performance which is better on your dell box which explains this performance difference