Adding database index for my Rails app

Question

I'm using MySQL and need to add some indexing to my tables for better performance.

One of my common queries is something like this:

arrs = @book.items.where("type = 'A' and status not in ('cancelled','notransportation')").order("item_date, item_time asc")

How would you suggest adding an index that would help with such a query?

Answer 1

Collect the raw SQL query that's being run from your application's logs, and then use SELECT EXPLAIN to figure out where to add your indexes.

I found this tutorial and this slideshow on SELECT EXPLAIN, which should help. Good luck!