Get both COUNT and LIMITed resultset

[penagate]

All,

I have a moderately expensive looking WHERE clause that I need to execute against a table. I would like to get both the total COUNT of all rows matching this clause, and a resultset bounded by a LIMIT clause.

At present I execute the query twice, once with COUNT and without the LIMIT clause, and once with the desired columns and with the LIMIT clause. This strikes me as inefficient and I would like to improve on it but don't know how to.

Is there any way of fetching both in one round trip?

Thanks,

— P

[si_the_geek]

The best I can think of at the moment is to run it without either, and put the results into a temp table - then run each of the Count/Limit on the temp table.


As you mention Limit, I assume this is MySQL.. as such, I'm not sure if you have SP's available (I expect you would, as you tend to stay up to date), or what functionality they provide. With SQL Server you could use a Table variable as opposed to a temp table, which may improve the speed (and as you can run all three steps in the SP, reduce the communications too).

[penagate]

Sorry, I totally forgot to mention the DBMS. I'm running MySQL 5, but this needs to be compatible with 4.

I thought of a temporary table or view as well but that would result in a huge number of them. This query needs to be run against potentially every row in a(nother) table.

I guess there is not really much I can do then other than omitting the COUNT if it becomes a performance issue or perhaps trying to cache the resultant set of IDs somewhere (though I'm not sure that would help at all).

Thanks.

[szlamany]

I have in the past used a sub-query into a select that is the exact same where clause but with a COUNT(*) as the SELECT list. This puts in each row the count of the total number of rows in the recordset.

I hope to think that the "query optimizer" sees that these two working resultsets arethe same an doesn't fully process - at least caches - the rows being processed.

What's LIMIT?

[si_the_geek]

From what I remember, Limit is basically the same as Top.

[szlamany]

Ok - then I'm guessing the COUNT is needed so that the LIMIT can be determined for the final resultset. And it's probably not something as simply as 50% or 25% (which TOP 50 PERCENT is allowed in MS SQL at any rate).

Then I agree with what Si said - build a temp table. It can be just the primary key's of the rows to get so that the complex WHERE clause only occurs in the building of the temp table.

Then the LIMIT'd 2nd resultset comes FROM the primary key's of the temp table - which should make the second fetch much faster.

[penagate]

LIMIT is analogous to TOP when used with one parameter, or can be used to specify lower and upper boundaries if the table has a numeric primary key. In this case, I'm using it to cap the result set to a maximum of N rows. I also would like to know the total COUNT, just for informational purposes.


Now that I think about it, a temporary table is probably a decent compromise for a stored procedure. It will still require two round trips, but at least the second should be much quicker than executing the first query again. The idea of storing just the primary keys is good too.

I wish I could work purely with MySQL 5 but that is not an option here.


Thanks guys.

[penagate]

Actually, the subquery idea is intruiging. I'll see if I can get some sort of query plan analysis happening on this.