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).
When we execute the query normally, MySQL jDBC driver fetches all the rows in one go. So your java will throw out of memory
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
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).
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.