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?