2

If I have a query that may return a lot of records and I want to know how many. How much the COUNT(*) will cost in memory ?
Is it always possible to use COUNT ?

Thanks in advance,

Regards, David

DavidK
  • 2,495
  • 3
  • 23
  • 38
  • 2
    What database are you talking about, MySQL or Postgres? And, more importantly, what do you mean? If you are confused about the `*`, it doesn't take up any (additional) memory; it is a convenience for the compiler and should be the same as `count(1)`. – Gordon Linoff May 23 '14 at 23:13
  • Also for MySQL it makes a difference what storage engine you are using for certain queries as one of them can get the unfiltered count from metadata. – Martin Smith May 23 '14 at 23:15
  • 4
    This question appears to be off-topic because it is about creating a collection of Do's and Don'ts related to SQL instead of about a specific problem. – Ken White May 23 '14 at 23:16
  • @Gordon Linoff MySQL but I think it will also be true for PostgreSQL. If I have a query I know heavy. If I replace what is supposed to be returned by the count of results, does this take a lot of memory ? – DavidK May 23 '14 at 23:16
  • 1
    @DavidK . . . It is impossible to make a generalization without seeing the queries that you are referring to. – Gordon Linoff May 23 '14 at 23:17
  • @Gordon Linoff It is just a question I was asking myself. About a database in Prestashop. If I want for each category of products the count of products per category. (And if this request has to be made each time the webpage is loaded, before showing the products.) – DavidK May 23 '14 at 23:22
  • The query I am thinking about is the Search query in Prestashop repeated for different categories of products just to have counts. https://github.com/PrestaShop/PrestaShop-1.4/blob/master/classes/Search.php (the find method that we modify to restrict for one category each time) – DavidK May 23 '14 at 23:25
  • @DavidK, with respect this question is a poor match for SO. That's because as written it's really open-ended and nonspecific. It also happens to be hard to answer in an open-ended way. That's for technical reasons having to do with how different RDMSs and storage engines handle record counting. "There is more in heaven and on earth than is dreamt of in thy philosophy, Horatio!" – O. Jones May 24 '14 at 01:16
  • @Ollie Jones. Yes, you're right it's nonspecific. It's close to the "is SELECT COUNT(*) expensive" that was linked to my question. My question was about multiple queries using COUNT on big datasets each time a webpage is loaded. It is a question I was curious about, so why not asking to SO community :) – DavidK May 25 '14 at 00:17

1 Answers1

1

Using MySQL withCOUNT(*) takes into account column indexes it will be the best result as long as there are no WHERE clauses etc

I think I read somewhere the MyISAM engine actually stores row count so it doesn't count the rows every time (instead it basis it on the primary key) -> I think I read that on another stack overflow question so try searching for it.

If you are grouping result sets do not use COUNT(*) but rather SQL_CALC_FOUND_ROWS after SELECT:

SELECT SQL_CALC_FOUND_ROWS FROM table LIMIT....

Maybe read this blog for more information on COUNT(*) vs COUNT(col) as well:

I've no experience of prestashop but maybe it helps, try testing the queries yourself and monitor the behaviour, speed etc.

EDIT: Here we are, this supports some of my statements above: is SELECT COUNT(*) expensive?

EDIT: When including a WHERE expression ensure your indexes are correct, consider adding a multi index (See how to create an index). Before this you can check your queries by using the EXPLAIN{query goes here} to see which index is used (EXPLAIN explained). If more than one, it can be optimised by moving the smallest index to the first position in the index - can't remember how to do this but Google will (How MyQSL uses indexes).

I'm trying not to go off topic here but it may also be worth considering adding triggers to create a summary table as is discussed here: Speeding up row counting in MySQL

Community
  • 1
  • 1
n34_panda
  • 2,577
  • 5
  • 24
  • 40
  • Thank you for the link. So what to do when we have a where clause (to have something powerful, so not to have to wait too long) ? I have the find function that is executed each time the page is loaded. (3 times if I have 3 categories) – DavidK May 24 '14 at 00:01
  • I've added a bit more information above. Summary Tables kept up to date via triggers maybe a way forward. When talking about performance what are you experiencing - how many rows and how many seconds ? – n34_panda May 24 '14 at 07:57