RE: Oracle and mysql
- From: "Crisler, Jon" <Jon.Crisler@xxxxxxx>
- To: <orasnita@xxxxxxxxx>, <joe_dba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Jul 2010 11:02:18 -0400
The reason that a "select count(*) from table "is fast in myisam, but
slow in Innodb, is that myisam tracks this metadata in its "data
dictionary", while Innodb does not. I never had a need for this info in
an application so I do not have a workaround, but if you are using 5.1
or higher, a trigger might help. MySQL has some support for stored
procedures.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Octavian Rasnita
Sent: Wednesday, July 14, 2010 4:25 PM
To: joe_dba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle and mysql
Hi,
I can't give you a good comparison because I am a pretty new Oracle
user, or better said, I have used Oracle for a longer time, but started
only recently to create databases with it.
I can give you a good example that was a reason of trying Oracle because
MySQL wasn't good enough.
I have a table that require using transactions which has more than 11
million records. That table is queried and also updated frequently.
I need to use a select count(*) from table_name and also a select
count(*) from table_name where column_name=1; where that column has only
2 possible values.
That select is extremely fast using MySQL and its MyISAM engine, however
MyISAM doesn't support transactions.
However, even without transactions, that query is slower when I need to
use that where condition no matter if there is an index on that column
or not.
So I have also tried the InnoDB engine which also supports transactions
but that query is extremely slow. It takes dozens of seconds.
With Oracle, which supports transactions, that query is very very fast.
Even if I would use MyISAM tables and MySQL, I think it may not work
very well under high load because MyISAM uses entire table locks so this
may slow those queries a little.
I know that I could use different hacks with MySQL and InnoDB for
keeping the total number of rows from that table in a separate table,
using a trigger, but in my app I use an ORM that uses a pager, and that
pager does a select count(*) and it might be harder to override what
that ORM/pager does... too hacky.
I have also tried using a select SQL_CALC_FOUND_ROWS ... in the main
query than use a select found_rows() but this takes a much longer time
so it is not an option either.
Oracle worked fine and fast in its "normal" way, even though... as I
said, I am an Oracle beginner.
Too bad that Oracle is much more expensive. :-)
Octavian
----- Original Message -----
From: "Joe Smith" <joe_dba@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, July 14, 2010 8:59 PM
Subject: Oracle and mysql
Does anybody know where there is a good comparison between Oracle and
MySQL?
I am interested in the limitations of MySQL against Oracle.
Or, I am not trying to start an e-mail war. I just need to know the
facts about why I would stick with Oracle over MySQL.
_________________________________________________________________
Hotmail has tools for the New Busy. Search, chat and e-mail from your
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:
ON:WL:en-US:WM_HMP:042010_1
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
