Hi everyone. I'm using MySQL 4.0.13 with W2K/SP4 on a 512KB RAM/1.2GHz Athlon (my dev workstation).
I'm having a strange problem: when I execute a query via the MySQL Control Center, it takes 65 seconds to return. About three seconds into the query, I issue a "mysqladmin processlist" and see the State as "sending data". To me, that means the query is done and the server is sending the data to the client (I'm running both on same machine). The CPU is only at about 4%, but the hard drive light is flashing like an XMas tree on crack.
I examined the query using "Explain" and it's using the correct index as I specified.
If I re-execute the query, it returns in .73 seconds!!!
I thought maybe it was cached by the client so I kill the client and execute it again - .73 seconds. I restart the server and do it again - .73 seconds. So it doesn't look like anything's being cached. This happens to other queries as well.
I just don't get it. Why the difference?
The table "test" has 1,000,000 records, so I'm definately not complaining about a subsecond response - but if the public hits this on the production server and I get the 65 second version, I'm screwed.
Any ideas????


Here's the query - because the users choose several options to build the query, it's dynamically built via a Java class:

SELECT UID,UserName,Gender,City,State,Zip,Country,Age,LastLogon FROM test USE INDEX (big) WHERE Status=0 AND Viewable=3 AND OnLine > 0 AND Age >=18 AND Age <= 99 AND Photo!='' AND Height <=66 AND gender IN ('b') LIMIT 250

The following columns are in the "big" multi-column index:
Status, Viewable, Online, Age, Height.

Thanks for any insight!