PHP + Mysql Select and Count

Question

I need get specific values and count all values from a MySQL table, i need get the best performance, my question is: What is better and faster?

- Use two separate queries:

 $TBCount = $Resps = $MySQL->query('SELECT COUNT(*) FROM T1');
 $Resps = $MySQL->query('SELECT id, name FROM T1 LIMIT 1');
 while ($Resp = $Resps->fetch_assoc()) {
    ...
 }

- Use One query with two SELECT:

 $Resps = $MySQL->query('SELECT id, name, (SELECT COUNT(*) FROM T1) AS count FROM T1 LIMIT 1');
 while ($Resp = $Resps->fetch_assoc()) {
    $TBCount = $Resp['count'];
    ...
 }

- Or someone have some best idea?

In the case of "One query, two SELECT", in the "while" loop, how can i get the count value outside of the loop? (to avoid unnecessary rewrite of the variable).

first thing first, you'd better to fix your syntax – Raptor May 29 '15 at 03:19 — Raptor, May 29 '15 at 03:19

score 1 · Answer 1 · answered May 29 '15 at 04:12

I would suggest first option with minor modification.

 $TBCount = $Resps = $MySQL->query('SELECT COUNT(id) FROM T1');
 $Resps = $MySQL->query('SELECT id, name FROM T1 LIMIT 1');
 while ($Resp = $Resps->fetch_assoc()) {
    ...
 }

note that I have mentioned just id in count query, this can be a good performance impact while dealing with large data.

You can do the same in second option, but as per my thought process second option will give count in all row returned with the main query. And that value will be same for all rows which is really not required.

That is the reason I would go with first option for separate queries.

iamdev · Answer 2 · 2015-05-29T04:02:00.017

0

Take a look at SQL_CALC_FOUND_ROWS. It does everything you want. It will give you the total results, even though you're limiting the data returned.

> SELECT SQL_CALC_FOUND_ROWS id, name FROM T1 LIMIT 1
> SELECT FOUND_ROWS();

edited May 29 '15 at 04:02

answered May 29 '15 at 03:29

iamdev

716
4
13

In the `SELECT` query, i not take all values, filter with LIMIT, and need count ALL values from table. – Manux22 May 29 '15 at 03:58
Easy fix. Just use column names instead of *. – iamdev May 29 '15 at 04:02
This will work like a charm but have u considered the time it would take. better to go with separate query for count and result. – Avinash May 29 '15 at 04:08
1

ref: http://stackoverflow.com/a/188682/148478 and https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/ – Avinash May 29 '15 at 04:21

Sourabh Kumar Sharma · Answer 3 · 2015-05-29T03:56:47.187

0

$Resps = $MySQL->query('SELECT id, name, (SELECT COUNT(*) as tot FROM T1) AS count FROM T1 LIMIT 1');
$Resp = $Resps->fetch_assoc()
$TBCount = $Resp['tot'];
mysqli_data_seek($Resps, 0);

for finding the count value you do not need to repeatedly loop again and again for the rest of the values use the while loop.

while ($Resp = $Resps->fetch_assoc()) {
  //rest of your code
    ...
 }

edited May 29 '15 at 03:56

answered May 29 '15 at 03:37

Sourabh Kumar Sharma

2,864
3
25
33

Yes, but `$Resps->fetch_assoc()` moves the pointer, and "while" start from second row... – Manux22 May 29 '15 at 03:55
@Manux22, yes you are right :), but we can set it back to 0 position using `mysqli_data_seek` function, check the edited answer of mine – Sourabh Kumar Sharma May 29 '15 at 03:57
it is more efficient that include this in the loop? – Manux22 May 29 '15 at 20:08
why i wrote this `$TBCount = $Resp['tot'];` outside the loop is because you need the total value only once why to store it repeatedly in the loop, if you feel you're suggestion is good then follow that :) – Sourabh Kumar Sharma May 30 '15 at 03:14

PHP + Mysql Select and Count

3 Answers3