[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.