Announcement

  •  » Requests
  •  » InnoDB versus MyISAM

#1 2012-07-10 00:45:26

fisharebest
Member
2012-07-10
6

InnoDB versus MyISAM

MySQL 5.1 defaults to MyISAM
MySQL 5.5 defaults to InnoDB

piwigo has "ENGINE=MyISAM" hard-coded into the CREATE TABLE statements.

I understand that there are some features that will only work with MyISAM (for example, full-text indexes), but I have searched the code and cannot find any of these features.

So why is MyISAM hard-coded?  Surely it is better to remove this clause, and allow each server to use its own defaults?

Also, would it be a good idea to add FOREIGN KEY constraints to the CREATE TABLE statements.  MyISAM would ignore these, and InnoDB users would get the extra protection against application errors and corruption.

Offline

 

#2 2012-07-10 09:45:05

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: InnoDB versus MyISAM

Hi fisharebest,

[Forum, topic 17063] InnoDB and mySQL

Do you know some benchmarks showing that InnoDB is as fast as MyISAM?

Piwigo does full-text search, with the quick search feature.

Offline

 

#3 2012-07-10 10:35:19

fisharebest
Member
2012-07-10
6

Re: InnoDB versus MyISAM

plg wrote:

Piwigo does full-text search, with the quick search feature.

I was refering to "FULLTEXT" indexes/searches.

ALTER TABLE table1 ADD FULLTEXT(column1);
SELECT * FROM table1 WHERE MATCH(column1) AGAINST ('word');

See http://dev.mysql.com/doc/refman/5.0/en/ … guage.html

This type of index is only available in MyISAM.

plg wrote:

Do you know some benchmarks showing that InnoDB is as fast as MyISAM?

It all depends on the server configuration, etc.  Many of the default
settings for MyISAM and InnoDB are very poor.

Also, InnoDB-1.1 (used in MySQL-5.5) is many times faster than InnoDB-1.0
(used in MySQL-5.1)

https://blogs.oracle.com/MySQL/entry/co … erformance
"InnoDB delivered 35x higher throughput on the Read / Write test and 5x higher throughput on the Read-Only test"

http://www.oracle.com/partners/en/knowl … 522945.pdf
"InnoDB delivered 35x higher throughput than MyISAM"

Offline

 

#4 2012-07-10 10:41:02

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: InnoDB versus MyISAM

For now, MySQL 5.5. is in minority (only 25% considering Wordpress statistics). I think it is a bit too early to switch, while there is no problem with MyISAM.

fisharebest wrote:

So why is MyISAM hard-coded?  Surely it is better to remove this clause, and allow each server to use its own defaults?

I'm quite sure we will encounter issues on InnoDB compared to MyISAM. So having some users on InnoDB and others on MyISAM only sounds like problems for me.

Offline

 

#5 2012-07-10 11:02:17

fisharebest
Member
2012-07-10
6

Re: InnoDB versus MyISAM

I'm quite sure we will encounter issues on InnoDB compared to MyISAM

I am the author of a large, open-source PHP/MySQL application (www.webtrees.net).
I switched to InnoDB about 2 years ago.  There were no problems.

or now, MySQL 5.5. is in minority (only 25% considering Wordpress statistics).

From my own statistics, I see approximately the same:
MySQL 5.0 = 25%
MySQL 5.1 = 45%
MySQL 5.5 = 30%

I also operate a small web-hosting service.  MySQL is highly tuned to run InnoDB.  I allocate almost zero memory to MyISAM.  All other popular web applications (WordPress, Joomla, MediaWiki, etc.) run perfectly with InnoDB - and they run faster.

I do not want to install piwigo (for one of my users) unless I can convert it to InnoDB.

To return to my original question - "is MyISAM *required*" - I guess the answer is no.

The easiest solution is to run this command before installation.

$ find piwigo -exec sed -i 's/MyISAM/InnoDB/i' {} \;

Offline

 

#6 2012-07-10 11:11:11

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: InnoDB versus MyISAM

fisharebest wrote:

To return to my original question - "is MyISAM *required*" - I guess the answer is no.

I don't know, the coding team has performed no real test.

fisharebest wrote:

The easiest solution is to run this command before installation.

$ find piwigo -exec sed -i 's/MyISAM/InnoDB/i' {} \;

Please tell us if it works fine :-)

Offline

 

#7 2012-07-21 00:49:47

Sven Peters
Guest

Re: InnoDB versus MyISAM

It gets even worse, some plugins dont specify the table-engine at all (e.g. BatchDownloader), so now I have a mix of InnoDB and MyISAM tables.

 

#8 2012-07-23 13:28:15

fisharebest
Member
2012-07-10
6

Re: InnoDB versus MyISAM

Please tell us if it works fine :-)

It works fine.

It gets even worse, some plugins dont specify the table-engine at all (e.g. BatchDownloader), so now I have a mix of InnoDB and MyISAM tables.

You can easily convert your MyISAM tables to InnoDB using the SQL command

ALTER TABLE xyz ENGINE=InnoDB;

Alternatively use phpMyAdmin (Table -> Operations -> Rename)

Offline

 
  •  » Requests
  •  » InnoDB versus MyISAM

Board footer

Powered by FluxBB

github twitter newsletter Donate Piwigo.org © 2002-2024 · Contact