インデックスの効かないSQLを書き換えて一部PHPで処理させようとした話
ORDER BYとLIMITを含むSQLを吐き出すCakePHPのコードがある。
// HogeModel $params = array( 'fields' => array( 略 ), 'conditions' => array( hoge_id => Configure::read('HOGE_ID') ), order => array( 'created' => 'desc' ), 'limit' => $limit ); $result = $this->find('all', $params);
このままのクエリだとインデックスが効かないが、ORDER BYを消したらインデックスが効くようになる場合。
// HogeModel $params = array( 'fields' => array( 略 ), 'conditions' => array( hoge_id => Configure::read('HOGE_ID') ), // order => array( // 'created' => 'desc' // ), 'limit' => $limit ); $result = $this->find('all', $params); $result = Hash::sort($result, '{n}.Hoge.created', 'desc');
最終的な$resultの中身を見るとなんか結果がおかしい。
- よく考えなくても当たり前だが、LIMITでレコード絞る前にソートした場合とLIMITで絞った後にソートする場合では結果が異なる。
- クエリを分離するなどして対策しなければならない(JOINして複数テーブルを対象にSELECTしてる場合は、それぞれのテーブルごとにクエリを分けるとインデックスが効きやすい(ような気がする))
LIMITをクエリから外して、PHPでソートした後LIMIT数分array_splice()するとか安直に思いついたけど、マッチした(大量の)レコード全部返すようなクエリはパフォーマンス的にまずいのかなあ
こんな感じで↓
// HogeModel $params = array( 'fields' => array( 略 ), 'conditions' => array( hoge_id => Configure::read('HOGE_ID') ), // order => array( // 'created' => 'desc' // ), // 'limit' => $limit ); $result = $this->find('all', $params); $result = Hash::sort($result, '{n}.Hoge.created', 'desc'); array_splice($result, $limit);