measure which queries are consuming the most time and optimize those queries first.
sometimes the solution is creating an index to make a query more efficient, sometimes it's modifying the application, ...
there is no general optimization solution.

There's also caching.

Cache common queries. If it's a forum, cache user info, since it's queried very frequently, several times per page, but fairly static.

[edited by: Gibble at 2:18 am (utc) on July 21, 2007]

Thanks for your answers.

What db is this?

If mysql you can run a mysqlcheck -o

This will optimize the tables a bit.

Maybe you need to tune the memory settings, or again if mysql the my.cnf file.

many times what slows a site down is disk access. this can be a huge bottleneck if your db is large. I worked on one site which was getting to be a real dog and found the disks were running at 95% all the time. I mounted 2 more disks and split up the db tables across multiple disks and the speed of the site tripled.

may be something to look into.

something else but not as common is the table size verse CPU cache. if the data is too large for the cpu then main memory bandwidth can become a bottleneck.

if you need to optimize SQL for better performance, this subject is a book in itself. using proper locks, efficient index use verse sequential access, optimizing table cache, etc. You will need an expert here to get things properly coded.

definately look into benchmarking the code to find where its bogging down...

Also don't forget to look at the documentation: [dev.mysql.com...] :)

As other people have said, you have to analyze your own situation to see where your specific bottleneck(s) is/are. (Also, optimization is an ongoing process as things change over time.) In addition to the other ideas of indexes (depending on your DB, you may need to periodically rebuild your indexes as well) and caching, other potential boosts can be gained from:
1) Restructuring your data- normalized data is not always the most efficient
2) Adding more memory
3) Upgrading to more powerful (or additional) CPUs
4) Moving to a dedicated server for your database (I assume you're already not using shared hosting- if you are, get your own dedicated server immediately, since you wil never have control over bottlenecks caused by other people on your box)