Re: getting totals with data - Erik Price - com.mysql.lists.mysql

3 messages in com.mysql.lists.mysqlRe: getting totals with data

From	Sent On	Attachments
Erik Price	17 Jun 2002 07:18
Galen Wright-Watson	17 Jun 2002 14:44
Erik Price	18 Jun 2002 06:49

Subject:	Re: getting totals with data
From:	Erik Price (pri...@hhbrown.com)
Date:	06/18/2002 06:49:36 AM
List:	com.mysql.lists.mysql

On Monday, June 17, 2002, at 05:44 PM, Galen Wright-Watson wrote:

Another option is to SELECT INTO a temporary table (or CREATE TEMPORARY ... SELECT), then query the temporary table for the total_hits.

Would the overhead of generating a temporary table for this query be worthwhile? I'm wondering, since the query will be executed by a PHP script (which of course has a number of other queries that execute along with it). Or would just having a query that strictly returns a COUNT of results, then a separate query returning the actual results (but constrained by LIMIT) be more resource-efficient... I wonder.

The reference to table1.total_hits is correct only if table1 has a column called total_hits. You don't need to (and can't) prefix a column alias by a table name (I think; a few small experiments seemed to confirm this).

This is useful knowledge, I somehow thought that aliased result columns could be treated like regular columns.

I just thought of another possibility if all you want is the number of matching rows. If you're using an SQL client, it should report the number of rows returned. If you're using an API to talk to the server, there should be a function to get the number of rows in a query result (e.g. mysql_num_rows() in the C and PHP APIs).

This would be perfect except that it seems that my LIMIT clause (which helps keep the number of results that are handed to the PHP script and turned into HTML low) would yield the LIMITed number of rows, whereas I'm trying to determine how many rows would be returned if I had not used LIMIT (the "total hits" part of "displaying X through X of X total hits").

Thanks for the pointers, Galen.

Erik

PS: to any who respond to this thread, please CC me as I have temporarily unsubbed the list.

----