How do you identify the best database indexes in Rails?

In Rails, I have been trying to build database indexes that speed up my queries, but I often find that after running a migration my database to build new ones, it still isn't using the indexes I'm building (even though they seem, to me, to be perfect for a particular query).

Is there a tool in Rails that can search through the system and identify specific indexes that need to be created for optimal performance?

If not, are there any specific rules that determine why my database is not using my lovingly craf开发者_运维问答ted indexes?

Essentially, my question is this: how can I avoid my current process, which is 1) run migration creating a bunch of possible indexes, 2) finding out they don't work, 3) rolling back and feeling slightly more frustrated at my database's apparent myopia?

---

Well, if all your queries are a form of SELECT * FROM, the indexes probably won't get used. Tune the queries first, so that you're returning only the data you need for each view and then build an index from there.

Also, you can run EXPLAIN on the database for every query, which will be a lot more illuminating than any gem/plugin the Ruby community can offer.

---

what database are you using? you should do some query log analysis so you can see what queries are being run, how often, how long they take, etc. ... a good tool for postgres is pgfouine. mysql has the slow query log so you can find the worst offenders. with that data in hand, you can run explain on the slowest queries, see what indexes make sense to add, etc.

can you provide some examples of slow queries and your current schema and indexes?

---

Check out https://github.com/eladmeidar/rails_indexes , which scans for missing indexes.

Related to query efficiency, but not necessarily indexes is https://github.com/flyerhzm/bullet , which notifies you of things like N+1 queries and eager loading opportunities.