InnoDB slow?

delpino

Hello all,

not directly related to PHP but I just switched a table from Innodb to MyISAM and suddenly the scripts runs at least 3 times faster. The table has about half a million entries. Is that normal behavior? I switched to Innodb for more security (MyISAM tends to crash once in a while).

NogDog

Yes, MyISAM's advantage is speed and the support of the FULLTEXT search capability. InnoDB sacrifices some speed in favor of transactions support.

delpino

Alright good to know, enough though the speed difference is really dramatic. I wish MyISAM would be self-repairing as InnoDB is.

NogDog

Without some careful testing, it might be hard to say how much of the speed increase was simply changing the storage engine and how much might have been simply the rebuilding of the table, perhaps resulting in some overhead clean-up and disk defragmentation, perhaps? (Pure speculation on my part, mind you.)

Weedpacket

delpino wrote:
I wish MyISAM would be self-repairing as InnoDB is.

Which would make it as slow as InnoDB is (you don't think InnoDB is just slower for the fun of it, do you?).

As NogDog hints, how often have you been cleaning your tables/indexes? If there were a lot of deletions, they can clog things up over time.

jazz_snob

Also, if you take out all the foreign key relations and transactions your querries will be of course be faster.

Did you try optimizing your querries before switching engines? eg: adding indices on your tables that "fit" the types of querries you run.

laserlight

Also, if you take out all the foreign key relations and transactions your querries will be of course be faster.

Foreign key constraints, maybe, transactions maybe not. Without transactions, it would be such that each individual SQL statement executed would run in its own transaction. Anyway, if you are not taking advantage of things like foreign key constraints and transactions, it is quite sad.