Cursor implementation in MySQL

07-17-2009, 01:50 AM

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

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 )