Re: Select count(*) in Oracle and MySQL

From: Jerome Vitalis <vitalismanN05P4M_at_gmail.com.invalid>
Date: 19 Sep 2007 20:10:12 GMT
Message-ID: <46f18224$0$14462$79c14f64@nan-newsreader-05.noos.net>

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 Received on Wed Sep 19 2007 - 15:10:12 CDT