It is so easy to forget to put index in the right places as you grow an application. Often times you will see an application degrade in performance as more users start using it. Usually some simple database tuning will go a long way in radically improving response times. Missing indexes on complex joins can speed things up by more than a factor of 100 in a lot of cases.
I.NFECTIO.US has created a query analyzer plugin that will output MySQL EXPLAIN statements for every query in development mode. Armed with this information it is easy to add indexes, reorder your joins and remove unneeded or redundant indexes.
script/plugin install http://svn.nfectio.us/plugins/query_analyzer