Re: 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/