Re: [PHP-DB] 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 <[email protected]> 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