I have an order by query that seems to be unpredictable in MySQL 5.0.
The table has about 400K rows. There are 3 columns of interest: 2 integer (i1 and i2), one varchar (name). I have a separate index on both of the integer columns.
The query is: “select i1,i2,name from table order by i1”. Explain indicates the filesort algorithm is in use. (The table is in fact presorted by i1, but we don’t assume that).
For timing I’m doing
time mysql -ABN -e ‘select i1,i2,name from table order by i1’ database > /dev/null
On MySQL 4.x the time is consistently between 2.5 and 3.5 seconds, even with a good amount of load on the machine.
On MySQL 5.0 the time varies much more. 1 in 10 measurements take about a minute. And we have no load at all on the 5.0 machine.
My OS is 64 bit Linux 2.6.9-34.0.2.ELsmp. We’ve tried several different installations of MySQL 5.
Any feedback appreciated, thanks. We use this logic a fair amount and want to decide whether to rewrite it.