MySQL Question - Limiting count()

[bradgrafelman]

And if you don't... let PHP do the work:

$sql = mysql_query("SELECT count(*) as MyCount 
FROM my_table"); 

$count = mysql_result($sql,0,'MyCount');

if($count > 1000)
     $count = 1000;

[Sayian]

Ok, thanks for the information.

Sxooter - is that sub query going to execute the same as if i did somthing like

SELECT * from my_Table?

b/c as you know, selecting ALL information from a table, is very slow.

Or, will it only count the rows b/c the count function was given first?

new to sub querys, so please let me know.

Brad - yeah, i thought about that, but as you know, anything mysql can do that php doesnt have to, saves time. Thanks for that input, and ill use that if the mysql option doesnt workout.

So, speed wise, which one do you think would be faster?

[bradgrafelman]

Well, theoretically, if you had a huge table, you'd want to limit it at the MySQL end, otherwise count(*) will have to count a lot of rows when you only want a max of (insert small percentage of huge # of rows here).

I wonder... would counting the number of rows returned from an indexed key be any faster? Like so:

$query = 'SELECT id FROM my_table LIMIT 1000';
$exec = mysql_query($query);

$numRows = mysql_num_rows($exec);

EDIT: Then again, I think MySQL optimizes count(*) to use indeces... hmmmm. We need either a MySQL guru that could tell us off the top of his/her head, or some benchmark results. I suppose I could set up a benchmark test and see how long it takes PHP/MySQL to do these solutions... blah, I'm so lazy though 🙁

[Plasma]

Sounds like a silly problem to have.

First off, "anything mysql can do that php doesnt have to, saves time." isnt necessarily true - your no doubt putting more strain on the sql server to process the request than a simple if check by php.

Why is the count so important, and why cant it exceed 1000? Perhaps theres a design issue you have instead.

[Sayian]

Well, im sure all problems you dont understand might sound silly to you.

The reasons for limiting the number of count() is for pagination reasons.

If 1000 pages are returned, then pages for 1000 results will be created.

If my count only returns 200 results, then pages will also be created accordingly.

With a table that holds millions of records, and a very active site, This "Silly" idea, might prove to be critical in maintaining my applications load.

IE: You may search on any or all fields.
Only 200 results will be returned.

• Sayian.

[bradgrafelman]

If you're doing this as part of a search query, why not just limit it in that query?

SELECT * FROM my_table WHERE `the_field` LIKE '$searchterm' LIMIT 200

Why figure out how many rows will be returned first?

[Plasma]

Sayin, I think you misunderstood what I was getting at, or I was not clear enough 🙂

I meant the problem of having to restrict the value 'count(*)' returned was silly (somewhat defeats the purpose).

Now that you specified its for pagination, I can help out some more.

Try this:

Query: SELECT SQL_CALC_FOUND_ROWS * FROM my_table WHERE the_field LIKE '$searchterm' LIMIT 200

Note the 'SQL_CALC_FOUND_ROWS' immediatley after the SELECT statement.

What this does is ask's the sql server to also retrieve the number of rows 'that would have been returned had you not specified a limit of 200'.

You can get access to that variable by doing SELECT FOUND_ROWS() immediatley after the initial query.

Here's an example:

// Issue a query, with the SQL_CALC_FOUND_ROWS attribute
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM my_table WHERE `the_field` LIKE '$searchterm' LIMIT 200";
$result = mysql_query($query);

// How many rows were returned with the limit clause
$shown_results = mysql_numrows($result);

// Set $total_results to the integer value of how many rows would have been returned had we not given a LIMIT clause to the previous query
$total_results = mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0, 'FOUND_ROWS()');

// Basic result count info
echo "Showing $shown_results of a total $total_results records";

[Sayian]

Plasma, thank you for the information, thats 1 step of what i needed.

Here is where the other problem lies.

$sql = mysql_query("SELECT count(*) as mycount 
FROM user_table");

$count = mysql_result($sql,0,'mycount');

// ok so lets say count found 35,000 rows in our users table

// setup pagination here, and build rows off of $count

// if we have 10 results per page, then their will be 3,500 pages genderated.

How to stop this when i only need 200 results returned?

For pagination reasons i think mySQL should build a count_limited() function

but, they dont.

So, i guess the best option now is maybe just having php do somthing like ..

// see if count is higher then max, if so lets mitigate it
if($count > 200) $count = 200;

Let me know your thoughts,

Cheers

[Plasma]

Do you mean you dont want any pagination at all if you get 200 results in total (ie, show them all on the one page?).

Or do you mean, if there was 200 results returned, dont paginate for 35,000 rows when your only wanting to know about 200 of them?

[Roger_Ramjet]

Don't see why you just don't do this

 $sql = mysql_query("SELECT count(*) as MyCount
FROM my_table");

$count = mysql_result($sql,0,'MyCount'); 
if ($count>200) { $count = 200; }

The you paginate for max 200 rows.

[Sxooter]

Ok, a few points.

If you're using myisam table, a straight select count(*) from a table is usually very fast. If you're using innodb, it has to examine the table itself and takes much longer.

Sayan, my query is likely to be more expensive than Rogers, but that depends on the database handler and size of the data set.

for an mvcc database (postgresql, mysql with innodb) select count() from sometable gets more and more expensive as the table gets larger, whereas select from sometable limit 200 has a linear performance response as the number of tuples in the table goes up.

So, this is one of those things where programming it one way (Roger's) makes MySQL fast, for myisam tables, and makes almost all other databases slow.

OTOH, programming it my way makes it middling slower than Roger's way, but it can be ported straight to oracle, firebird, postgresql, or mysql innodb with no need for a different query there.

MySQL with innodb, postgresql, and all other mvcc type databases know to shortcut a select with a limit, so my query won't be horrifically expensive. However, in databases like oracle and postgresql which can store a table in multiple files for efficiency, you should probably limit yourself to one field, not *. I.e.:

select count(*) from (select idfield from sometable limit 200) as a

if id is an integer, and a primary key, it will give you the same answer, but if the database is storing the larger text fields and such in that table in a separate file (toast tables for postgresql, partitioning for Oracle) then it will be pretty fast.