Well first thing to do is start by using the EXPLAIN SELECT syntax, start checking out what the hell is going on. If you're running a JOIN syntax, you want to avoid the 'ALL' type, your goal should be to get eq_ref. Also beware of the 'INDEX' type, which preforms the same as ALL, but is a little faster.

You also want to look at the number or writes you have to do the database, and consider if your I/O volume does require you to replicate, or cluster. Obviously at some point you reach the limits of your DB hardware limitations. And when that happens, bad things happen.

I'd also look into the schema of data, if you find that you're running a lot of complicated searches or something of the like, you should cache the search queries.
I worked before on a site, where when one person searched for a term, that was cached, everyday the cache starts over. So if 'Bob' searches for 'panda images', he finds what he wants, and the server caches those results. Then, if 'Jill' searches for 'panda images' as well, she gets the same results, just without any extra load on the server.
There's an infinite number of possibilities of ways to cache data. I'd recommend checking into a way that is most adaptable to your site or projections needs.