以前書いた「グループ内の上位 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。
