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
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
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
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
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
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
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.
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
plg wrote:
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.
Hello,
12 years later, Piwigo still hasn't migrated to InnoDB. Is there a reason for this?
I myself have a gallery of over 3 million photos, and there will probably be over 5 million soon. The extreme slowness of certain queries that I've noticed lately (even to the point of MySQL using 100% of the processor and crashing) disappeared as soon as I converted the tables to InnoDB.
Thanks in advance for your reply.
Offline
I'm more than ready to migrate from MyISAM to InnoDB - just wondering - have you created any manual/custom indexes or have you simply just converted every table to InnoDB ?
Offline
Made the move today and migrated TWO Piwigo databases from MyISAM to InnoDB - both seem to work perfectly, no issues searching items or anything similar.
Offline