Different ways to count record numbers in Mysql

Asked

Viewed 53,244 times

6

There’s a difference in performance if you do:

//seleciona todos os itens da tabela
$cmd = "SELECT * FROM produtos";
$produtos = mysql_query($cmd);

//conta o total de itens
$total = mysql_num_rows($produtos);

instead of running a query: SELECT count(*) FROM produtos to count the number of occurrences in the database? NOTE: disregard mysql/mysqli usage

php mysql sql

4 answers

18


The basic technique for counting records is:

SELECT COUNT(*) FROM tabela

Has two reasons:

  • This will only run the count (a very short single line) and not all table data. This makes a huge difference.
  • And yet it is optimized in Mysql. He won’t even do any counting operation in this case, he takes the cache number he already keeps with this information.

I’ve already done a question about this.

  • ,@bigown is possible to sql sequinte? SELECT Count(name),durancao FROM products

  • Yes, it’s possible, but you lose most of the advantage. This way the count should be made and will traffic a much greater amount of data than just the count . But if you need this data there is no better solution. But this is already a gain in worry to bring everything.

  • A way that I didn’t know worked but seems to work, is MAX(ROWNUM).

  • 1

    @Gustavocinque I do not know well the implementation of this, if there can be gap between numbers. I don’t know if it’s reliable. Do you have any information that shows that it is? Not to accept results that are only correct by coincidence.

  • I don’t have, @bigown. As far as I know, ROWNUM is the line count of the result, and with MAX() i get the highest value of lines. I see this function being widely used by frameworks based on jsf (richfaces for example), where the maximum result of a grid is calculated in this way, and also the limit of the pages is calculated, using them in clauses WHERE.

  • I’m looking for this in the Mysql documentation and I’m not finding anything, even if there is this. Although I understand that if it exists it should work as you explained. I just don’t know if there is gaps in a sequence that makes the last line number the same as the line count.

  • Sorry, confused, rownum is not from Mysql. It is oracle.

  • As far as I can see, there is no way to reproduce this pattern in Mysql...

Show 3 more comments

4

There is. When you run the SELECT * FROM produtos, In terms of performance you need to wait for the database to load all the data and deliver it to PHP. Then PHP will count the data. Already the second case SELECT count(*) FROM produtos the bank itself gives you only the amount of data existing in the bank. Much more efficient in counting.

4

Response based on Ricardo’s comment on Big’s response. If you want to return a record limit and know the total how you used: SELECT count(nome),duracao FROM produtos, as an alternative, I think in that matter you can find some help.

mysql> SELECT SQL_CALC_FOUND_ROWS duracao FROM produtos LIMIT 10;
mysql> SELECT FOUND_ROWS();

The output will be something like the array below - note that it returns the total of records regardless of the limit informed in consultation:

Array(
    [0] => 50
    [1] => Array(
            [0] => Array( [duracao] => 1 )
            [1] => Array( [duracao] => 2 )
            ...
        )
)

3

Absolutely. Imagine the memory used to get 1000 products just to know the quantity. Run a count will return only one record, representing a major improvement in memory consumption.

  • Memory consumption is a factor that helps the performance to be worse but is not by far what affects it most.

  • Exactly. 1000 products in communication... It is better to go to lunch and come back. By the way say that Select * From should be avoided whenever possible.

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.