Mysql – Efficiently querying a very large MySQL table with C#

MySQL

I am issuing a relatively simple SQL query in my C# app:

select * from myTable where myValue > 10 order by myValue;

There are ~180 million rows in the table and there is an index on myValue. When I run the query in MySQL Workbench, it returns the first 1000 rows in 0.015 seconds.

When I run the query in C#, it times out.

I suspect that the MySqlDataReader and/or the MySQL engine is attempting to find all matching rows before returning.

What I really want is read one row at a time from my C# code. I want to queue up this query and just pull rows off the top one at a time as they're needed.

Is there an appropriate programming paradigm for this in MySQL?

Best Answer

Workbench tacks on a LIMIT clause. And, since you have the perfect index for such, it stops short.

How many rows are you expecting? What will you do with that many rows?

It is probably possible for a C# client to configure the connection show that the rows will be delivered as they are found, thereby showing you some output before it timesout. Such is rarely used because delivering giant resultsets is rarely practical.

Related Question