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