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.
Thanks very much. I am indeed seeing a correlation in Handler_read_rnd_next and Handler_write. When I have a typical query (2.5 - 3.5 seconds), those values increment by 10 or so. (I see they are a rolling sum). When I have a slow query (50 seconds or so), they increment by over 100.
It also looks like if I pause a second or 2 between sending queries, the performance stays at the desired level (2.5 - 3.5 seconds). It seems I can mostly get the long queries by sending a query just after the first one finished. So perhaps the filesort is doing some cleanup in the time just after the first query returns, and somehow I am interrupting it by sending the second query so soon.
We don’t have to worry about this case in our application logic. Still, it isn’t something I saw in my 4.0 testing.