size limit of a mysql query ruby/mysql

Question

Hello everyone, I hope this is the appropriate place to ask my question.

My mysql query currently looks like this

@records = Record.find(:all, :select => "`records`.id, records.level as level,
  (SELECT (count( b.id ) + 1)
  FROM records as a, records as b
  WHERE a.id =  records.id and b.skill > a.skill and b.created_at ='#{vandaag}' ) as ranktoday,
  (SELECT (count( a1.id ) + 1)
  FROM records as a1, records as b1
  WHERE a1.id =  timestamp1.id and b1.skill > a1.skill and b1.created_at ='#{timestamp1}' ) as ranktimestamp1,
  records.skill as skill, worlds.title as world, chars.name as name, vocs.voc as vocation, timestamp1.skill as timestamp1",
  :conditions => ["`s1`.title = :skill AND `records`.created_at = :vandaag ", {:skill => params[:id], :vandaag => vandaag, :timestamp1 => timestamp1}],
  :joins => "
  LEFT OUTER JOIN `skilltypes` as `s1` ON `s1`.id = `records`.skilltype_id
  LEFT OUTER JOIN `records` as `timestamp1` on `timestamp1`.character_id = `records`.character_id and `timestamp1`.created_at = '#{timestamp1}'
  LEFT OUTER JOIN `characters` as `chars` ON `chars`.id = `records`.character_id
  LEFT OUTER JOIN `vocations` as `vocs` ON `vocs`.id = `chars`.vocation_id
  LEFT OUTER JOIN `worlds` ON `worlds`.id = `chars`.world_id",
  :limit => "500",
  :order => "`records`.skill DESC"

To me it's a quite long query and i'm afraid all these joins are causing my problems.
And if i'm right the order statement causes mysql to join all the records with the tables, and after that result only the first 500, instead of ordering the records first, limiting and then start joining it. You understand?

And the problem wouldn't be that big if i had only 1000 records, but currently i got 380000 records and daily i'm getting another 21000 records.

My actual question is how do I find out if it's better to split joins in different mysql queries?
Or is my mysql query just a mess and working on your laughmuscles and make me look like a fool?

I'm kind of stuck here since the loading time is horrible.

Greetz, Rikkert

Edit: And I've got indexes on characters.world_id, characters.vocation_id, records.character_id, records.skilltype_id, records.skill, records.world_id which i think should be enough Greetz, Rikkert

Answer 1

My first question is, do you have indexes set up? Any place in your query that you have a comparison, you probably should have an index set up on the columns involved.

Answer 2

I'm definitely not a MySQL expert, but in my experience I have found that using sub-SELECTs can be the source of some performance troubles.

I try to avoid using them, but it isn't always possible to do so.

This SO has some more info you might find useful:

http://stackoverflow.com/questions/341086/mysql-subselect-performance-question

It also talks about the EXPLAIN command which I use in these situations to try and diagnose troubles.

Answer 3

First, it looks like you're not really using active record as intended. Let it manage associations between tables for you. I'm guessing that there's likely a way to get the information you need without resorting to that much manual sql building. Apologies if I'm just picking on something you edited for your example.

Second, use EXPLAIN to understand how mysql is executing your query.

Mysql's query planner is very simplistic and often surprises people who are used to Oracle, etc. The mysql online documents have some good information on how Mysql uses indexes.

A common pattern for optimizing complex queries is to de-normalize: that is, use an observer to maintain a second table of exactly the information you want (basically a materialized view).