I have a fairly simply query that is taking a long time when run from C# via MySql.Data.MySqlClient.MySQLCommand
but the same query runs about 100x faster when I run it through the MySQL Workbench (GUI utility)
Here's the code in C#:
tCom.CommandText = "(SELECT ID, Timestamp FROM jobdata WHERE `Timestamp` >= "
+ (dCurTimeStart - dTolerance).ToString()
+ " ORDER BY ID ASC LIMIT 1) UNION "
+ "(SELECT ID, Timestamp from jobdata WHERE `Timestamp` <= "
+ (dCurTimeStart + dTolerance).ToString() + " ORDER BY ID DESC LIMIT 1) "
+ "ORDER BY ABS (`Timestamp` - "
+ dCurTimeStart.ToString()
+ ") DESC LIMIT 1";
And in the MySQL Workbench in a .SQL file:
(SELECT ID, Timestamp FROM jobdata WHERE Timestamp >= 40967.44041508102 - 0.002 ORDER BY ID ASC LIMIT 1) UNION (SELECT ID, Timestamp FROM jobdata WHERE Timestamp <= 40967.44041508102 + 0.002 ORDER BY ID DESC LIMIT 1) ORDER BY ABS(Timestamp - 40967.44041508102) DESC LIMIT 1
Method 1 takes about 13 seconds, method 2 takes 0.l seconds. As far as I can tell they are the exact same query - the parameters in the C# snippet are set to the same numbers used in the raw sql method (dTolrance is 0.002 and the dCurTimeStart is 40967.44041508102).
Both methods work on the same DB table and connection. The 'jobdata' table is indexed by timestamp and ID in a binary tree.
Anyone have any ideas?