title: size limit of a mysql query ruby/mysql
body: <p>I hope this is the appropriate place to ask my question.</p>
  
  <p>My mysql query currently looks like this</p>
  
  <pre><code>@records = Record.find(:all, :select =&gt; "`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 &gt; 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 &gt; 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 =&gt; ["`s1`.title = :skill AND `records`.created_at = :vandaag ", {:skill =&gt; params[:id], :vandaag =&gt; vandaag, :timestamp1 =&gt; timestamp1}],
    :joins =&gt; "
    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 =&gt; "500",
    :order =&gt; "`records`.skill DESC"
  </code></pre>
  
  <p>To me it's a quite long query and i'm afraid all these joins are causing my problems.<br />
  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?</p>
  
  <p>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.</p>
  
  <p>My actual question is how do I find out if it's better to split joins in different mysql queries? <br />
  Or is my mysql query just a mess and working on your laughmuscles and make me look like a fool?</p>
  
  <p>I'm kind of stuck here since the loading time is horrible.</p>
  
  <p>Greetz, Rikkert</p>
  
  <p>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 </p>