0

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?

Improve this question
4
  • FYI - If you're trying to edit the question, you'd be best off if you register your account; then, you can edit the question without an approval process (you may have to do some sort of merge process to get everything working). – RDFozz Sep 20 '18 at 15:59
  • 1
    When I run the query in MySQL Workbench, it returns the first 1000 rows in 0.015 seconds. Enable General log and check what queries were sent by Workbench REALLY. – Akina Sep 20 '18 at 18:27
  • Like @Akina hinted at, workbench is probably adding a LIMIT clause. Perhaps you can say what you want to do with 180 million rows in a sequential manner in C#. Maybe there's a database away to do something that at least partially helps. What MySQL version do you have? – danblack Sep 22 '18 at 8:29
  • Suggest maybe you can use a LIMIT clause too, and once you've process the first batch, save the myValue (and maybe a unique id) and use that in the second query (instead of 10). I'm hoping this isn't a queue of my Tax return, I'd like it before I die. – danblack Sep 22 '18 at 8:33
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.