Speeding up ActiveRecord Querying on very large joins?

As someone else mentioned if you can cache it, cache it.

That said... am I reading that right that in one of your queries you are joining on 13 different tables?

- Find the SQL produced for that query and run it through your database using whatever necessary to get the query plann (ie. EXPLAIN SELECT.....). Make sure you've got indexes on the columns that need them.

- Play around with breaking that query up into smaller pieces. It may be that splitting them into individual queries and "building the join" yourself turns out to be faster. This is particularly true if your DB isn't picking up the indexes and if you have massive tables, but are only returning a very small set of data (and again your DB isn't picking up the indexes and lopping things off from the get go).

If you're using MySQL it can be very frustrating when you have the right indexes, but it chooses not to use them. Look to see if you can force it to or give it hints.

Philip Hallstrom wrote:

That said... am I reading that right that in one of your queries you
are joining on 13 different tables?

- Find the SQL produced for that query and run it through your
database using whatever necessary to get the query plann (ie. EXPLAIN
SELECT.....). Make sure you've got indexes on the columns that need
them.

- Play around with breaking that query up into smaller pieces. It may
be that splitting them into individual queries and "building the join"
yourself turns out to be faster. This is particularly true if your DB
isn't picking up the indexes and if you have massive tables, but are
only returning a very small set of data (and again your DB isn't
picking up the indexes and lopping things off from the get go).

If you're using MySQL it can be very frustrating when you have the
right indexes, but it chooses not to use them. Look to see if you can
force it to or give it hints.

Here's the new pastie:

http://pastie.org/574754

It appears that the indexes are being used now? I see the rows returned
down to 1 - 3 rows which is much better than seeing 400+ rows on each
column in the first segment.

However, on the EXTRA it says that the rest are using WHERE but I don't
specifically saying it's using index. I see the key indexes it shows
but shouldn't it be saying "Using where; Using index"?

The time appears to be a bit better - went down from 14 seconds to 5
seconds but still bottlenecks a bit. I might have to do as you said -
break up the two tables that I'm joining 14 tables on each..

Offense joined with 14 tables (bottleneck begins to occur)
Defense joined with 14 tables (bottleneck is massivly apparent)

All the other joins are 0.0 or 0.1 ms. So, I think my best bet is to
break these up into smaller...

I broke up the offense, defense joins in half and ran a new test...

20 ms (winner)..

I still get all my data so with the indexes in place, the join splits,
it looks good - I'll have to do some extensive testing though to see how
it load balances in production..

Many thanks everyone. I really appreciate the help with this
optimization.