Re: Select count(*) in Oracle and MySQL

From: Anurag Varma <avoracle_at_gmail.com>
Date: Wed, 19 Sep 2007 13:51:45 -0700
Message-ID: <1190235105.521143.247410@r29g2000hsg.googlegroups.com>

On Sep 19, 4:10 pm, Jerome Vitalis
<vitalismanN05..._at_gmail.com.invalid> wrote:
> On Wed, 19 Sep 2007 11:40:21 -0700, Occidental wrote:
> > I have a table with about 250M rows, implemented in both Oracle and
> > MySQL.
> > Select count(*) in MySQL is effectively instantaneous, presumably
> > because it accesses some internal count that is maintained by the DBMS.
> > The same query in Oracle takes about 6 minutes, pretty obviously becasue
> > it counts rows. The Oracle table should have been set up with a primary
> > key, but wasn't. The MySQL table has no primary key either. Any
> > comments?
>
> Please change the Storage Engine your MySQL database uses to something
> nearer a professional and reliable RDBMS such as Oracle -- e.g. change
> the default MyISAM to InnoDB, and come back with the result of your count
> (*).
>
> It's all in the doco of MySQL. count(*) is optimized with MyISAM *only*.
> And MyISAM is not transaction-safe.
>
> Jerome

In addition to what Jerome said, take a look at this: http://www.postgresql.org/docs/techdocs.83

Its a postgresl vs mysql performance details .. however, should answer your question.

Anurag Received on Wed Sep 19 2007 - 15:51:45 CDT