Cursor implementation in MySQL

MySQL & MariaDB Other MySQL® Questions
1

Hi everybody,
I am using MySQL 5.0.67 in for a client. We use JDBC to connect and retrieve data from MySQL

In one of our scenario we have a query which retrieves a large result set. But the application retrieves chunk by chunk(ex:100 rows in one go).
Our observations

  1. When we execute the query normally, MySQL jDBC driver fetches all the rows in one go. So your java will throw out of memory

  2. WE tried server cursor(using useCursorFetch property). But in this case also, MySQL creates temporary table/file in the server and stores all data in that. This also takes lot of time even minutes depending on number of rows and data size to get the first row

  3. We tried with streaming result set feature supported by MySQL JDBC driver(that’s it retrieves one row at a time). But here also we faced issues(I reported this to mYSQL forum:http://forums.mysql.com/read.php?39,250629,250629#msg- 250629).

  4. We tried the MySQL feature LIMIT also. But here also we found that LIMIT 0,100 comes quickly but LIMIT 100000,100 takes lot of time.

So my question to all our geeks is, What is the best way to get a subset of records from a large result set? How can I craete a cursor like behavior without affecting performance/memory much?

Note: I tried this same situation in Oracle. It worked without having any of the mentioned issues.

Hope somebody can guide me.

Sunil

2

Hi Sunil,

Pretty easy question.

When you say limit 100 000, 10, that means (as you know)mysql will read 100k+10 rows and return the last three. Solution is pretty simple, find a column to avoid this. Use an autoincrement column or something what can allow you to execute query like

… and id > 100000 and id < 100010

Mysql is not as smart as oracle, but with one column and a simple index this would work like charm for you )