[RESOLVED] how to count properly?

[rulian]

Okay, so I'm stumped on a problem that i've done a million times, but this time I want to do correctly.

Simply page navigation for searches, limiting number of rows per page.
Of course I need to know the number of rows matched, but also limit the number of rows queried.

The problem of course being select * from blah limit x,y, my mysql_num_rows($query) will be y if y or more rows are found, so I cant divide the pages.

I'm running this on an indexed, but still rather large table with query times .12 each.
So for me to run 2 queries, 1 to count and 1 to retrieve would be impractical as each request would at to about .25 seconds, or 4 requests per second, with alot of uses on a high usage table this is certainly a trouble spot.

I'm thinking of using EXPLAIN on the count and retrieving rows info, but of course that count isnt always accurate.

Any suggestions on how to go about this problem?

Actually, I just realized, the EXPLAIN count will return the number of rows examined, not matched, so it's useless

[nemonoman]

even a large table, properly indexed, should run a query in less than .12 seconds. RETURNING all the rows, or a portion of all the rows, might take that long, but returning a COUNT(*) may take much less.

I'll just assume you've done your due dilligence, therefore, and tell you what I would do. Let's assume you want to return 50 rows per page. I'd query all the rows, but only run a loop to display the first 50.

After that, I'd add buttons that would call the 'next 50' type query (Limit 50, 50; limit 100, 50; etc). Keep track of the original total number of rows by passing the original value to the next page using Post or GET methods.

[rulian]

is there any method of count that doesnt transverse the table? I think count does right?

The most I can think of it to keep a record track in another table, which works fine for mini forums, but for something liek user search, I need to get the latest count, so each time I search I dont want to have to return rows i've already used or wont use since that could eat up alot of load time. if anything I think i can run the coutn query once and store it's results to eliminate some.

or to take your suggestion, and simply limit 0,50 lets say to the query, and run it until it returns less or zero, but then I cant have page numbers.

[rulian]

actually count will still only count the number of rows returned, so I can try a subquery on count, but that's still 2 queries

[nemonoman]

Any query will find all the rows, then based on LIMIT return only the requested rows from all SELECTed.

So "SELECT FROM table WHERE type='xxx' " and "SELECT FROM TABLE WHERE type='xxx' LIMIT 20" will BOTH find all rows where type='xxx'.

$result=mysql_query("SELECT * FROM table WHERE type='xxx' ");
$total=mysql_num_rows($result); //like 40000 rows
$i=0;
while($i<50){
$row=mysql_fetch_array($result);
$i++;
}

This should be very fast.
If your query is slow, speed up your query with query tuning. Post the query.

[Sxooter]

A select count(*) can be cheap or expensive depending on the db you're using.

For mysql - myisam it's pretty fast and cheap.

for mysql - innodb / postgresql / oracle not so much.

[rulian]

its' innodb, i was using it for row level locking,
I could try myisam, but i'd like to stay with innodb

[nemonoman]

myisam returns an ultrafast count() if and ONLY if the select is of all records...Thus a myisam
SELECT COUNT() FROM myTable
is ultrafaster than innodb

But
SELECT COUNT(*) FROM myTable WHERE myColumn='A'
Will be about the same compute time on both innodb or myisam tables

[nemonoman]

From the MySQL manual:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

For example:
mysql> SELECT COUNT(*) FROM student;
This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB and BDB storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count

[rulian]

yeah I think i'm just going to have to bite the bullet on this one and run 2 queries. Or do some sort of incremental navigation that is independent of result numbers, and simply have nothing found on these records past the row count

[Roger_Ramjet]

Strike that, query cache is no use to you here.

What will be if great use though is SQL_CALC_FOUND_ROWS

[rulian]

Roger:
I think you nailed it,
when I did

SELECT SQL_CALC_FOUND_ROWS * FROM `tabe` WHERE `call` = '203' LIMIT 0,50;
SELECT FOUND_ROWS();

it returned the number of matches!
and it ran the query in 1/10 of the time the 2 queries worked in. thanks alot!