[dasil003]

No, caching should be the last trick you use to optimize.

Indices are standard way to achieve basic performance levels of a database. They may have their downsides but "potentially dangerous" is dramatically overstating the case. Furthermore, the dangers of premature optimization are about taking extra time or adding complexity to something that ultimately doesn't matter, not about using very basic features in a sane way.

The correct optimizations to make first are ones that make the biggest impact percentage-wise as well as being the most elegant in the code. Indices typically fit both categories very well. Unless you are doing a lot of stupid things, there's not going to be much lower-hanging fruit, but even if there is, after you apply sane indices that will be when your profiling will start to reveal the real interesting possibilities for optimization. The idea that indices are a good final optimization does not show much interest in real performance.

[nostrademons]

I would take swombat's advice to look at query patterns, eliminate repeated queries, and tweak your algorithm to require less data well before I thought about adding indices (or caching, which I agree is the last thing you should do).

You want to make the riskiest, most invasive changes first, because those are the ones that the rest of your codebase builds upon. If you've changed your query patterns and the app still isn't fast enough, it's relatively trivial to add indices on top of that. The speed benefits are cumulative, and none of the work you've done examining your data-access patterns has been undone by making that data access faster.

If you add indices first, however, and it still isn't fast enough, you have to examine your query patterns anyway. And this time, the work you did is undone by your further optimizations. The benefits of indices depend a lot on how you access your data: they slow down writes to speed up certain queries. If it turns out that the queries you're speeding up don't occur frequently anymore, your index is counterproductive.

(That said, since adding indices is often a task that takes 5 minutes of your time, you might want to give it a try and see what sort of results you get before investing a lot of effort in other stuff. If it doesn't work, you can back out the change and then start looking at your query patterns.)

[mikescar]

Disagree on caching as the last thing you do.

If you're building an app that will hopefully be bigger than you expect, build cache into your data layer, it's not complex and will pay off sooner than later.

[cageface]

You should be thinking about indices as you design your data model. You're going to put them in eventually so the ad-hoc performance testing you're doing as you're building the site should at least somewhat reflect the final, real-world scenario. Better to know sooner rather than later that your data model is so broken that even extensive indexing can't make your queries fast.