MySQL Forums
Forum List  »  Performance

resultset rows performance
Posted by:
Date: September 14, 2012 02:16AM

Hi,

I'm facing some performance problems related to retrieve the length of a query.
My application, like all apps, needs to paginate query results, to do this I need two things:
1. the abbility to get x results starting from y
2. the total number of results of the query

Initialy, i've developed a generic solution based on jdbc. Basically, what I did was to run the query normally and take advantage of the scroll-insensitive resultset.
1. Moving to the end and getting rownum to know query length
2. Moving to start and looping to page num to get only a page
This was a good solution for quite a number of databases, it's based on internal resultset operation: fetch size defines the number of rows going from the server to the client. This allows me to run a query, left results on server, and work with resulsets getting only those rows i need.

NOT with MySQL! MySQL's jdbc driver gets all results to the client independently of fetch size.

Ok, there's no problem... MySQL is a good enviroment, i want to work with... let's find a diferent solution... and i founded it:
1.- execute same query with count(*)
2.- use MySQL SQL's limit start, rows
It was fine, althought i had to run two queries (previous solution runs only one) it has a good performance... The problem comes with large tables: it takes a really long time to get the count !!!

So, second solution doesn't work neither...

What can i do ? it's possible to resolve this situation in an elegant an eficient way with MySQL ? i'm doing something wrong ?

Options: ReplyQuote


Subject
Views
Written By
Posted
resultset rows performance
1704
September 14, 2012 02:16AM
853
September 14, 2012 03:54AM
851
September 14, 2012 07:49AM
807
September 14, 2012 10:29PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.