ソート対象カラムの置き換え ●CakePHPは各テーブルにidがつく(Primary Key) ○createdのソート順とidのソート順は基本的に⼀致する ●createdのソートをidのソートに置き換え SELECT * FROM works ORDER BY created DESC LIMIT 30 SELECT * FROM works ORDER BY id DESC LIMIT 30
複合インデックスによる改善例 SELECT * FROM payments WHERE created BETWEEN '2019-01-01 23:59:59' AND '2019-06-30 23:59:59' AND operation_type IN ('reward', 'external_reward') AND user_id = 122864 ORDER BY id DESC ●本番環境で25秒かかるクエリ
インデックスが効かなくなる例(UNIONクエリ) ●※status, createdにそれぞれ単⼀インデックスが付与されている場合 ○2つに分割してそれぞれインデックスを効かせる SELECT id FROM results WHERE work_id = 1 AND status IN ('worked', 'working') UNION SELECT id FROM results WHERE work_id = 1 AND created > '2019-10-01' SELECT id FROM results WHERE work_id = 1 AND status IN ('worked', 'working') SELECT id FROM results AS Result WHERE work_id = 1 AND created > '2019-10-01'
インデックスが効かなくなる例(SQLで条件分岐) ●IF、CASEで条件を分岐しているパターン ○条件の処理はPHP側で⾏う SELECT * FROM payments WHERE user_id = 1136075 AND CASE WHEN belong_to IN ('TimechargeInvoice', 'Milestone') THEN operation_type IN ('withholding_client') ELSE operation_type IN ('withholding_lancer') END; SELECT * FROM payments WHERE user_id = 1136075 AND operation_type IN ('withholding_client') SELECT * FROM payments WHERE user_id = 1136075 AND operation_type IN ('withholding_lancer')
CakePHPのページング機能の弱点 ●ページ後半の検索が重くなる ○LIMIT句のOFFSET値が上がるほど遅くなる SELECT * FROM proposals ORDER BY id DESC LIMIT 0,40 SELECT * FROM proposals ORDER BY id DESC LIMIT 3467480, 40 これは軽い 激重
⽂字列検索の問題 ●LIKE検索は前⽅⼀致しかインデックスが効かない ○MySQLのFULL TEXT INDEXはスペース区切りしか対応していない SELECT * FROM works WHERE title LIKE '%PHP%' OR description LIKE '%PHP%' 中間⼀致は インデックスが効かない
MySQLの全⽂検索 SELECT * FROM works WHERE MATCH (title) AGAINST ('PHP') OR MATCH (description) AGAINST ('PHP') MySQL 5.6 MySQL 5.7 EC2 on MySQL スペース区切り ※MroongaはN-Gram、Mecabをサポート スペース区切り N-Gram 形態素解析(Mecab) RDS for MySQL スペース区切り スペース区切り N-Gram Aurora スペース区切り スペース区切り ●MATCH 〜 AGAINST構⽂でサポートされている全⽂検索機能 ○※インデックスが1つしか使えない点は考慮が必要 ■複雑な条件で検索し始めると⾏き詰まる