サブクエリーにLIMITを使った履歴系テーブルの高速化(MySQLでマテリアライズドビュー3 番外編)

履歴系テーブルは基本的にページャーでページ分割することがほとんどだと思います。前回のエントリーでもLIMIT句を使って30行だけ取ってきている例となっています。今回の問題点は、巨大なテーブル×巨大なテーブルのJOINをしようとするから重いのであって、予めどちらか一方のテーブルのデータを絞っておけば相当早くなるはずです。そこで、大本となるcommentテーブルで先にLIMITをかけてしまいます。

SELECT SQL_NO_CACHE 
  comment_id , 
  fu.user_name form_user_name , 
  tu.user_name   to_user_name , 
  comment 
FROM 
  (SELECT * FROM `comment` LIMIT 900000,30) cm,user fu,user tu 
WHERE 
    fu.user_id=cm.from_user_id 
  AND  
    tu.user_id=cm.to_user_id

30 rows in set (3.48 sec)

最近はサブクエリーにLIMITが使えるようになったんですね...。それともFROM句だったらLIMIT使えたのか。以下はMySQL 4.1.1の人用にサブクエリーをテンポラリーテーブルに変更した例です。

• サブクエリーにLIMITが使えない場合にテンポラリーテーブルを使用した例

CREATE TEMPORARY TABLE t_comment (
  PRIMARY KEY(comment_id) ,
  INDEX(from_user_id)     ,
  INDEX(to_user_id)       
)
SELECT * FROM `comment` 
LIMIT 900000,30;

SELECT SQL_NO_CACHE 
  comment_id , 
  fu.user_name form_user_name , 
  tu.user_name   to_user_name , 
  comment 
FROM 
  `t_comment` cm,user fu,user tu 
WHERE 
    fu.user_id=cm.from_user_id 
  AND  
    tu.user_id=cm.to_user_id