Re: Dramatic performance drop when table > 100MB

[Patrick Questembert]

I should have mentioned which operations are slow ... here is more information:

There is no issue with updates actually, i.e. "INSERT INTO mytable ...." - that works just fine and add rows to the table just as fast whether table > 100MB or not.
The problem is with the queries, this is the code:

IDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = "SELECT msgid FROM mytable WHERE msgid="+W.MsgID+";";
IDataReader myReader = catCMD.ExecuteReader(); // This is the slow command
myReader.Read();
...
myReader.Close();
return;

The statement which takes a very long time is the catCMD.ExecuteReader() call, not accessing the reader after that. It takes between 4 and 5 (!) seconds to complete.
Note also that if I run the program twice in a row, this statement will not be slow for the rows already accessed the previous time I ran the program, i.e. the results seem to be cached and it's only when MySQL actually needs to go and run the query that it takes time.

And it's not an issue of updating the table and reading from it at the same time - even a simple series of queries such as above with no updates shows that problem (for tables > 100MB).

Table is InnoDb, I am using MySQL 4.1/WinXP and the ODBC 3.51 driver.

Thanks!
Patrick