You might not like this . . . but the first place to look is your programming. This is usually where mysql begins to tank, in the way it's accessed.
I'll give you one example: one of the most common things I see in PHP is
execute a query to meet a where condition, getting the data
execute a SECOND query for mysql_num_rows
Loop through num rows, make repetitive selects for each record id to actually get at the data that was in hand already in the first query. I don't know where this logic comes from, but I see it a lot.
Another example is select * from when all you need is one or two fields.
I almost never see mysql_free_result() after queries, which frees up what? memory. :-)
You can add query benchmarks throughout your code, compare against command line queries, this will tighten up the programming and reveal possible areas of improvement.
Explain on your queries can tell you a lot too.
Inadequate error checking often leads one to think "it's working fine" when it's getting dangerously close to crashing your server.
For the DB itself, question a few items:
Are there any areas that I could be using integer lookups instead? Example, instead of
select * from products where category like '%some_cat%'
select * from category where category=5
where even though "category" is in a separate table, queries on integer fields will always run faster. Use this to your advantage. At every chance in programming, try to eliminate text searches, even if it means creating tables or complicated selects with joins. Save text searches for when you need them.
Are there any columns that have redundant data that doesn't need to be?
John owns 4 sites. Each site is id'ed by a unique ID. So we have to have row after row of redundant data . . . NOT!
Again, eliminating 4 rows in the users table, and allowing integers to rule the sites table, this is going to be light years faster, even with a join.
Are my columns wider than they need to be?
create table . . . zipcode varchar(255) <------
Are my columns indexed? Be careful with indexing, use it only on fields that will be searched in a query. Indexing all fields can sometimes have the opposite effect.
create table . . . zipcode varchar(15), index (zipcode(3))
On that topic, is my column the appropriate type? If your site commits to a five digit zip code, U.S. only (bad idea, but follow along) why would you want anything but this?
create table . . . zipcode int(5), index (zipcode(3))
Google for mysql normalization which will lead to many topics on tightening up the ship.