sqlite3データベースのファイルサイズは114,570,240バイト、テーブル数は1件だけです。ファイルサイズはそこそこあるんですが、１テーブルのみのデータベースで960件ぐらいの登録数です。この程度ならケア不要かと思っていたんですが、間違いでした。

SQLite3 1000件未満のテーブルでselectに1.78秒かかる

1. sqlite3のバージョンは3.7.17。
   
2. スキーマはこんな感じです

   
   CREATE TABLE tbl( pk INTEGER NOT NULL PRIMARY KEY, 
   sts, model,year, post_id, time, lapse, 
   mokuji, html, error_reason, fatal_reason );

   
   
3. 問題になったのは「select * from tbl order by time, year desc」の発行です。
   約1.78秒かかりました。サーバーの負荷が高い場合は30秒近くかかることがありました。
   
4. どうすべきか？処理の見直し、EXPLAIN QUERY PLANを使って調査、原因追求して対処するしかないですね。
   処理自体見直しましたが、これといっておかしな部分はありませんでした。
   

1000件未満のテーブルでselectに1.78秒かかる原因はUSE TEMP

SQLite3は「EXPLAIN QUERY PLANコマンド」でSQLの問題点（というかどんな感じに処理しているのか）を見つけることができます。（詳しくはこちら＝＞https://www.sqlite.org/eqp.html）

調査中データベースに変更を加える事になるので、対象のデータベースファイルをコピーしたものを利用しています。

1. PKが効いているパターンはこんな感じにシンプルな結果になります。

   explain query plan select * from tbl;
   0|0|0|SCAN TABLE tbl (~1000000 rows)

   
   
   
2. 遅いSQLはB-TREEのテンポラリを使ったソートを使っていることがわかります。

   explain query plan select * from tbl order by time, year desc;
   0|0|0|SCAN TABLE tbl (~1000000 rows)
   0|0|0|USE TEMP B-TREE FOR ORDER BY

   このUSE TEMPを消すことができれば速く改善できます。
   
   
3. インデックスはtime,yearは不正解でした。

   sqlite> create index idx_1 on tbl( time, year );
   sqlite> explain query plan select * from tbl order by time, year desc;
   0|0|0|SCAN TABLE tbl (~1000000 rows)
   0|0|0|USE TEMP B-TREE FOR ORDER BY
   sqlite> drop index idx_1 ;

   結果は変わりません。
   
   
4. order句に指定しているすべてを対象にします。time,year descが正解です。

   sqlite> create index idx_1 on tbl( time, year desc);
   sqlite> explain query plan select * from tbl order by time, year desc;
   0|0|0|SCAN TABLE tbl USING INDEX idx_1 (~1000000 rows)

   USE TEMPが消えました。
   

まとめ：indexを適切に設定することで改善できます。EXPLAIN QUERYは万能ではないです

phpで「$開始=array_sum( explode(' ', microtime() ));処理;echo array_sum( explode(' ', microtime() )) - $開始;」とmicrotime関数を利用すると精度の高い実行時間を計測することが可能です。

↓で1.78秒かかっていました。EXPLAIN QUERY PLAINで見つけたindexで0.088秒に改善することができました。

            $results = $db->query("select * from tbl order by time asc,year desc"); 

今回の知見：データベースファイルサイズが大きいと、USE TEMPを利用するパターンは速度的な影響をモロに受けることがわかりました。



EXPLAIN QUERY PLANはクエリがデータベースインデックスを効果的に利用しているのか？、インデックスを設定することで速くできるのか？という視点で調査する場合に使えるコマンドです。