Re: Re: Table optimization ideas needed

Yes, Chris. You are right. I think I mentioned in the archive that the table is Innodb engined. Maybe Roberto didn't notice that. On Thu, Mar 27, 2008 at 7:26 AM, Chris <dmagick@gmail.com> wrote: > 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/ > -- Regards, Shelley