1

The accepted way of getting the number of results from a JDBC result seems to be, to do resultSet.last(), and then resultSet.getRow(), according to this answer. But, in that answer, the author also says:

it may not be a good idea as it can mean reading the entire table over the network and throwing away the data. Do a SELECT COUNT(*) FROM ... query instead.

I'm looking for a definite answer on this. Performance wise, will it be better to do a separate COUNT(*) query to get the number of results, or will it be better to do resultSet.last() and resultSet.getRow(), followed by resultSet.first() again?

If the ResultSet has already fetched the results and is holding them in memory, then it'd undoubtedly be better just to do last() and getRow() as (I assume) it would just loop over the results in the memory. But the OP's answer above seems to imply that it lazy loads the results from the db as they're requested.

Community
  • 1
  • 1
Ali
  • 261,656
  • 265
  • 575
  • 769
  • Just my thought. If your end goal is to count the number of results why would you parse each row of the table to get a count. Is this extra work really required? – Prateek Oct 19 '13 at 19:13
  • possible duplicate of [MySQL: Fastest way to count number of rows](http://stackoverflow.com/questions/5060366/mysql-fastest-way-to-count-number-of-rows) – Joe Oct 20 '13 at 09:52
  • The point that the author of that answer was making is that, in some JDBC drivers, `ResultSet#last()` will point you to the end and you are **unable** to use `ResultSet#first()` because the pointer will take you to the end of `ResultSet`, thereby exhausting it. I've seen this happen. In some JDBC situations, `ResultSet#next()` is really the only way to iterate through. In these situations, `ResultSet#first()` really doesn't even work. – ryvantage Dec 13 '13 at 20:54

2 Answers2

1

Making a separate query is not a good solution (on my opinion) because server will search rows twice for one result set.

To prevent "double" query MySQL has function FOUND_ROWS(), it returns a number of rows founded for current conditions from WHERE clause. This is very useful when you use LIMIT and OFFSET in the query. I believe that using this function is a better solution. http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

I had read some time ago that JDBC streams rows from server, but this was not official documentation and I can't tell how it works in the present, but queries can be different - with difficult sub-queries and joins. I think that server can finish all resources if will "stream" these rows only when client asked for them.

Nicolai
  • 5,489
  • 1
  • 24
  • 31
  • `found_rows()` doesn't seem to be any good for any queries which don't use limit. – Ali Oct 19 '13 at 23:20
1

According to me it depends on your use case. If you resultset is small enough then the performance difference would be negligible. I highly doubt that the resultset fetches the whole result in the main memory for larger resultsets. To second this here is the link that talks about this particular scenario. In such case calculating size using last() , getrows() followed by first() would be obviously inefficient as it has to first load the portion of resultset in memory and perform these operations(and don't forget the network transfer time) to calculate the net size. On the other hand count(*) would just go in and count the rows in your result set.

This is my understanding of which one out performs other. I am open to any inputs from others

Prateek
  • 1,916
  • 1
  • 12
  • 22
  • The MySQL JDBC driver by default fetches all rows at once. – Mark Rotteveel Oct 22 '13 at 09:06
  • @MarkRotteveel By fetching do you mean that it transfers the whole result set into the mail memory? – Prateek Oct 22 '13 at 18:49
  • Yes, it is one of my annoyances with the MySQL driver. See specifically http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-implementation-notes.html under `ResultSet`: _"By default, ResultSets are completely retrieved and stored in memory."_ – Mark Rotteveel Oct 22 '13 at 18:52
  • @MarkRotteveel Thanks for the link. This is one of the biggest design flaws that I have came across in MySQL driver. – Prateek Oct 22 '13 at 18:53