To tweak, or not to tweak? (mysql)

Assuming you have a cluster of mysql servers serving your application, and you have configured them to share the load, yada yada, your next step is probably going to be asking 'how can I improve the design of database schema?'

Here are some links which cover that field (may I remind you to backup your database before starting, follow them at your own risk, and you cannot hold me responsible for any resulting issues!)

This one illustrates tweeking a Joomla CMS application on a VPS (shared hosting server):
http://www.joomlaperformance.com/articles/server_related/tweaking_mysql_server_23_16.html

This one discusses the differences between innodb and myisam db engines:
http://worthposting.wordpress.com/2008/06/22/innodb-or-myisam/

This appears to be the command to convert a myisam (the default) table to an innodb one, if you wanted to persue that, but beware the monolithic file may need more proactive maintenance and monitoring (for corruption etc, but not fragmentation issues). Again BACKUP!!

-- "ALTER TABLE foo ENGINE=InnoDB"

This one discusses locking, and mysql transactions - no transactions on myisam though! (hence you might need innodb for that)
http://forums.whirlpool.net.au/forum-replies-archive.cfm/498624.html

There's also the problem of race conditions: two select queries (two mysql sessions, webrowsers etc) return the same row, which the application wants to update. The first to 'accepted, the second to 'rejected' - row or table locking will prevent them from writing the new value at the same time, but what happens if both processes complete in lockstep, first 'accept' ing, then 'reject'ing the same row - if only one possible outcome was acceptable? (in other words, a row cannot be accepted after it has been rejected, and vice versa.) We could prevent accidental overwrites by increasing the criteria of the where clause:

UPDATE `orders`
SET `status`='accepted'
-- the first where clause identifies the row
WHERE `order_id`='".$this->DB->quotestring($order_id)."
-- and the second helps prevent 'logical' data collisions!
AND `status` != 'rejected'';

Lastly, this one illustrates a number of different ways one could design a schema
"including discussions on schema architecture, common data access patterns, and replication/scale-out guidelines" using web 'tagging' as an example.

http://forge.mysql.com/wiki/TagSchema