mysql - mysql 查询 ruby​​/mysql 的大小限制

Question

我希望这是问我问题的合适地方。

我的 mysql 查询目前看起来像这样

@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"

对我来说这是一个很长的查询，我担心所有这些连接都会导致我的问题。
如果我是对的，那么 order 语句会导致 mysql 将所有记录与表连接起来，然后只得到前 500 个结果，而不是先对记录进行排序，限制然后开始连接它。明白了吗？

如果我只有 1000 条记录，问题就不会那么大，但目前我有 380000 条记录，而且每天我都会收到另外 21000 条记录。

我的实际问题是如何确定在不同的 mysql 查询中拆分联接是否更好？
还是我的 mysql 查询一团糟，让你大笑，让我看起来像个傻瓜？

我有点卡在这里，因为加载时间太长了。

你好，Rikkert

编辑:我在 characters.world_id、characters.vocation_id、records.character_id、records.skilltype_id、records.skill、records.world_id 上有索引，我认为应该够了 Greetz, Rikkert

Answer 1

我绝对不是 MySQL 专家，但根据我的经验，我发现使用子 SELECT 可能是一些性能问题的根源。

我尽量避免使用它们，但并不总是可以这样做。

此 SO 包含更多您可能会觉得有用的信息:

MySQL subselect performance question?

它还讨论了我在这些情况下用来尝试诊断问题的 EXPLAIN 命令。