I can’t comment on foreign keys but moving to InnoDB would be a good idea for large scale. Although it’s a little slower than MyISAM, it does row level locking, rather than table level locking. As a result saving a post or doing a lengthy operation or query won’t lock the posts table, freezing the … Read more
WordPress actually specifies no table type in the create statements, so it uses MySQL’s default table engine which is InnoDB from version 5.5 onwards.
MyISAM locks the whole page for writes and is not ACID compliant, while InnoDB sticks to locking rows as you point out, and is ACID compliant. This makes it sturdier when you’ve a lot of writes, as in faster and less prone to data corruption. It’s slower for reads in my experience, though it handles … Read more
I would indeed switch to InnoDB. Table locking/row locking has long been discussed by many. I would always choose InnoDB hands down. However, there is another profound reason for choosing InnoDB…CACHING. While most people boast that MyISAM is faster for reads, most people forget that the many cache for MyISAM, which is called the key … Read more
This is done with Read more about it here. ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for MyISAM, BDB, and InnoDB. This statement works with MyISAM, BDB, InnoDB, and NDB tables.
I found the generated set of drop statements useful, and recommend these tweaks: Limit the generated drops to your database like this: Note 1: This does not execute the DROP statements, it just gives you a list of them. You will need to cut and paste the output into your SQL engine to execute them. Note … Read more
For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database: You will need to adjust the permissions after that. For scripting in a shell, you can use either of the following: OR Notes: There is no space between the option -p and the password. If your … Read more