I’ve developed a 55k product ecommerce site using WordPress with the Shopp plugin and can share what I’ve done to MySQL to eke out better performance, YMMV and some (or all) of these may not apply to your situation.
Determine how much you need to increase buffers/caches by by looking at the output from a “show status” sql command – there are tools which pretty up this output which may be useful, I often just use phpmyadmin to get an idea. http://blog.mysqltuner.com/ is a handy resource and utility too.
Make sure your query cache is on and that it’s large enough to have a very low low memory punes number. Make sure your the number of key reads is not too high (this being a relative value), increasing key_buffer_size can help with that. Make sure that the number of temporary tables being created is low, reduce that number by increasing tmp_table_size.
Turn on the slow query log and log slow queries, re-run those queries manually with explain statements, add indexes change column types, etc, as needed. For one query we were getting insane explain statements (as in comparing many, many more rows than it “should” have been), upgrading to a newer version of MySQL caused that same query to be much better optimized (and much quicker) by MySQL itself.
If you’re using full-text indexing I don’t believe InnoDB tables are an option – just don’t update products during your busy times. If your ecommerce package supports it you can minimize customer disruption (performance or otherwise) by making changes on your staging server and then moving over just the product tables with a dump from stage and loading onto production – this will not work without some additional work for the rest of your WordPress installation however.
If your site design and UX can support it guiding the users into a “browsing” mode vs. a “searching” mode (eg: clicking around a site vs typing searches into the search box) can help you take advantage of some very easy database level and WordPress level caching.
Pre-load your caches – easy caching like query caches and WordPress-level caching can also be pre-loaded by spidering your site with wget. You can also pre-load the cache for searches by using user search behavior (via your logs) and re-getting those requests when you need to fill the caches.