Speeding up ActiveRecord Querying on very large joins?

For readability, this is better to look at:

http://pastie.org/574570

Are you already using Memcached? If not, you'll want to. Your data
doesn't change very often, so one strategy you might consider is kicking
off a job that runs your queries in the 'wee hours' of the morning-after
so they're cached for your users.

HTH,
Bill

bill walton wrote:

For readability, this is better to look at:

http://pastie.org/574570

Are you already using Memcached? If not, you'll want to. Your data
doesn't change very often, so one strategy you might consider is kicking
off a job that runs your queries in the 'wee hours' of the morning-after
so they're cached for your users.

HTH,
Bill

Hi Bill,

thanks for the response mate. I haven't checked out memcached and will
go look at that now.

The problem for running my queries is that there are 120 teams so that
would mean approximately 14,400 quries since any two teams can be
matched up and compared to one another. I'm not sure that would work.
Even without any type of stoppage time, I'm probably looking at around
90 minutes to run all those queries?

Maybe I can do something like that. Without optimization (can't quite
think of anything further I can do to optimize it (unless memcached will
work) - the only thing further I think that would help would be a
multi-tiered server setup with the database sitting on a server by
itself and using nginx as a front end on another server and my content
on yet another server.

I've been contemplating this but right now it would be fairly expensive
and I don't have enough time to implement it before the start of this
season.

On the table where the bottleneck is deepening the following is with
explain:

http://pastie.org/574630

You need to add quite a few indexes to those tables then.

Alpha Blue wrote:

Yeah,

I'm using innodb so I thought that the indexes were automatically setup
when specifying _id on any column. I was mistaken.

Um, yeah. :slight_smile:

Also, consider switching to PostgreSQL. You'll get better performance
without sacrificing referential integrity.

Best,

If you setup foreign key constraints, then the indexes will
automatically be created on this [parent]_id columns. (And InnoDB
doesn't let you remove them even if you wanted to.)