Re: [PHP-DB] Re: Table optimization ideas needed

Roberto Mansfield wrote:
> Shelley wrote:
>> Hi all,
>>
>> I made a post a week ago to ask for the idea of the fastest way to get
>> table records.
>> Fyi,
>> http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table
>>
> 
> Hi Shelly,
> 
> I question your mysql database setup. I have a log table with about 2
> million records which I used for comparison. Here are the queries you
> mentioned in your link above:
> 
>> SELECT COUNT(*) FROM test_table;
>> SELECT COUNT(id) FROM test_table;
>> SELECT COUNT(1) FROM test_table;
>>
>> The results goes here:
>>
>> mysql> SELECT COUNT(*) FROM test_table;
>> +----------+
>> | count(*) |
>> +----------+
>> | 20795139 |
>> +----------+
>> 1 row in set (1 min 8.22 sec)
> 
> A count(*) against the entire table does not scan all rows so this
> should be very fast. In my case, a full table count was about .06 seconds.

You're assuming she's using a myisam table - which will indeed be fast. 
Switch to an innodb table (or falcon if you're feeling adventurous) and 
you'll have this issue because they support transactions and are prone 
to MVCC issues.

-- 
Postgresql & php tutorials
http://www.designmagick.com/