[TYPO3-dev] Tip: use SQL function COUNT(*)

[Fabien Udriot]

Hi,

Funny, I did recently some research about this topic. :)

First hint:

Innodb is *not* slow for ALL COUNT(*) queries but only for very specific case of COUNT(*) query 
without WHERE clause.
http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

Second hint:

In MySQL there is an option to get "automatically" the number of row in one request. This is 
convenient in a page browser when using a LIMIT.

e.g.

SELECT SQL_CALC_FOUND_ROWS  firstName, lastName FROM my_table LIMIT 10
SELECT FOUND_ROWS()

However, I was wondering whether the performance is improved this way as there is only one request 
sent to MySQL instead of 2. It appears no.

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

 From the article:
"It is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS. you send 2 
requests to MySQL: one for getting the records, the other is for counting"

Have a nice weekend,

Fabien

Ernesto Baschny [cron IT] a écrit :
> ries van Twisk wrote: on 18.07.2008 21:39:
> 
>>> A result set is not "data" but just a pointer to some structure on the
>>> server which can be be used to retrieved data with the "fetch" commands.
>>> The data is not pre-fetched by mysql or the client.
> 
>> If you look at the PHP's code that directly calls libmysql.
>>
>> Then libmysql get's the number of records from it's internal dataset 
>> here:
>  > (...)
>> then read_query_result is called to fetch all data into memory from 
>> the mysql server
>> see here : 
>> http://leithal.cool-tools.co.uk/sourcedoc/mysql509/html/mysql_8h.html#a115 
>>
>>
>> So that means it's not a cursor or anything but the whole dataset is 
>> wetched into lib_mysql and mysql_num_rows
>> simply reads a value from it's internal array and doesn't call the 
>> server, nor mysql_fetch_row calls the server
>> to get data from the server. It's all memmory based in libmysql.
> 
> Oh my gosh, you are right. Thanks for pointing that out!
> 
> Cheers,
> Ernesto