Client Side Cursors: this method returns the client the whole result set upon a SQL request.
What is Faster?
Client Side Cursors: once you get the result set back to the client, you can manipulate it and read through it using minimal communication with the server.
So Why Should I Choose Server Side Cursors?
If you have a very large result set (say 100GB and millions of records), it might not be wise to fetch all the record set and only then start manipulating it. Lucene is one of the most common use cases for it.
Server Side Cursors are the solution in this case. Instead of fetching the whole result set to the client, the result set is being stored in a server's In Memory table. Upon request, the server returns to the client a defined number of rows. That way the client can start processing the results, while further fetch is done over the network.
What are the Limitations?
- Server Side Cursors support forward only result sets (you must set in JDBC for example fetchSize="NUMBER" resultSetType="FORWARD_ONLY") .
- MySQL Server Memory. This method requires a lot of memory. Fortunately, if you don't have enough memory, MySQL will automatically use MyISAM table to store the temporary table.
How should I Configure It?
Client Side Cursors need 0 configuration since it's the original behavior of MySQL.
The Server Side Cursors feature was added at MySQL 5.0 and is nowadays considered stable. Yet, you will have to enable it in your connection definition, and while fetching
Can I Use Them Both on the Same Connection Pool?
Yes. Server Side Cursors are used only if two terms accomplished: 1) Server Side Cursors are enabled (useCursorFetch) and 2) setFetchSize is positive (>0). Therefore, you can enable Server Side Cursors by setting setFetchSize to positive value and set fetchsize to 0 to keep using Client Side Cursors. If you are tired of setting setFetchSize to 0 every time, just set defaultFetchSize to 0.
Bottom Line
Now, you have the tools and you can better use MySQL for your cloud highly scalable system.
Keep Performing,
Moshe Kaplan
