読者です 読者をやめる 読者になる 読者になる

グループ内の上位 N 件を抽出する SQL はストアドでいいんじゃない?

以前書いた「グループ内の上位 N 件を抽出する SQL がうまく書けない(或いは SQL を show status で解析) - ngの日記」ですが、アプリケーション側でループで回す・・・というかストアドプロシージャでやればいいのでは? と思ったのでやってみました。Oracle で言うところの表関数に相当するものが無さそうなのでちょっと微妙な感じですが・・・

テンポラリテーブルを使わない版

複数の結果セットが返るためアプリ側で next_result とかしてやる必要があります。

drop procedure if exists sp_xxx;
delimiter //
create procedure sp_xxx()
begin
  declare _gid int;
  declare _val int;
  
  declare _cur cursor for
    select G.gid, (
      select B.val from users B
      where B.gid = G.gid order by B.gid, B.val limit 9,1
    ) AS val
    from groups G
  ;
  
  declare exit handler for not found close _cur;
  open _cur;
  
  loop
    fetch _cur into _gid, _val;
    select * from users where gid = _gid and val <= _val;
  end loop;
END
//
delimiter ;

flush status;
call sp_xxx();
show status like 'handle%';
show status の結果
Variable_name Value
Handler_commit 17
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 1
Handler_read_key 83
Handler_read_last 0
Handler_read_next 320
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 17
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 16

テンポラリテーブルを使う版

1 つの結果セットで返りますが、テンポラリテーブルで MEMORY ストレージエンジンを使っているのでテンポラリテーブルのサイズに気をつける必要があります。

drop procedure if exists sp_xxx;
delimiter //
create procedure sp_xxx()
begin
  declare _gid int;
  declare _val int;
  
  declare _cur cursor for
    select G.gid, (
      select B.val from users B
      where B.gid = G.gid order by B.gid, B.val limit 9,1
    ) AS val
    from groups G
  ;
  
  drop table if exists _tmp;
  create temporary table _tmp engine=memory select uid from users where null;
  
  begin
    declare exit handler for not found close _cur;
    open _cur;
    
    loop
      fetch _cur into _gid, _val;
      insert into _tmp select uid from users where gid = _gid and val <= _val;
    end loop;
  end;
  
  select * from _tmp inner join users using (uid);
  drop table _tmp;
  
END
//
delimiter ;

flush status;
call sp_xxx();
show status like 'handle%';
show status の結果
Variable_name Value
Handler_commit 35
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 1
Handler_read_key 244
Handler_read_last 0
Handler_read_next 320
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 178
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 176

サブクエリやらなんやらで頑張ったのが馬鹿らしくなる程度の速度になりました(2 sec → 0.05 sec )。MySQL のストアドプロシージャなど初めて書きましたが、これも選択肢の 1 つかもしれません*1

*1:PL/SQL や T-SQL と比べて MySQL のストアドはショボすきる気がしてたので今まで使ったことがありませんでした