そこで簡単に速度比較をしてみようとおもい、実はその時点で今使用しているサンプルのデータベースを作ったのですが、とりあえず全件取得方式と前回のSQL方式とで比較してみました。
といっても素人なので（笑）ちゃんとした？ベンチマークの取り方とかよくわからずになんとなくスクリプトの先頭付近の時間と最後の付近の時間の差を取ってみました。
結果は我流SQLの圧勝でした！(^^)v まあ元々の全件取得方式が何秒単位（5秒とか）という検索アプリとしては致命的なくらいの遅さだったのでナンですが、まあとりあえずコンマ何秒、場合によっては小数点第二位まで0があったので個人的にはヨシとしました。
但しあくまでも全件取得方式と比較してでの話で、採用しなかった「カウント方式」や「SQL多発方式」とは比べていません。なのでもしかしたらその方が速いかもしれません。おいおい暇があったら計ってみるかもしれませんが。
で、今回はこの我流SQLをちょっと手直ししてみました。といっても速度改善とか“ぱふぉおまんすちゅうにんぐ”とかいう高尚な作業ではなく、単に自分で気になってた部分を書き換えただけなんですが…。
まずJOINですがモノの本（謎）によるとJOIN *** ON *** とかINNER JOINとかレフト？とか“あうたぁ？”とかなんとかいろいろ書いてあるわけです。自分はプログラムはもちろんSQL（っつかDB）も素人なので今までWHERE句でキーを連結？していて「あ〜これで連結すんだな」程度にしか考えていなかったのですが、どうもWHERE句というのは本来はフィルタ？というか絞込み？というのか要するにちゃんとした？本来の？検索条件を書くための場所のような感じで、JOINの条件はON以降に書くのがなんとなく正統派のような気がしました。（？が多いな）
んで、まあWHERE句でやってるJOINは普通の？っつーか一番一般的？っぽいINNER JOINということらしく、さらにINNER JOINの場合は特にINNERとかつけなくても単にJOINでいいらしく、さらに条件はONの後に今までWHERE句に書いてあったことを書けばいいらしい…ということが判明したのでそのように書き換えてみました。
FROM newcity n
JOIN joined j ON n.id = j.newcity_id
JOIN oldcity o ON o.id = j.oldcity_id
JOIN pref p ON p.id = n.pref_id
サブクエリのFROM句のみですがだいたいこんな感じです。やってることは同じなんですがこっちのほうがすっきりするというか、ぱっと見何と何のテーブルを連結しているのかがわかりやすいかなぁ？と。
んでここまで書いてみて（SQLを）フとおもったのが「毎回毎回JOINするんかぃ？（何故か関西弁チック）」ということで、これもモノの本によるとVIEWという機能があるらしい。しかもMySQLでもVer.5以上だとサポートされてる云々…。
というわけでVIEWについて調べてみる。すると要するにクエリーに名前を付けて保存してるだけだ…と、んで使うときは既存のテーブルみたいに扱える…と、でも実際そういうテーブルが存在するわけではないので正規化が崩れるようなこともない…と、スクリプトはすっきりする…と。
とまあ以上のようにいいことづくしらしいので即採用決定
CREATE VIEW city AS
SELECT
p.name pref,
n.id nid,
n.city nc,
yomi,
o.id oid,
o.city oc,
gdate
FROM newcities n
JOIN newcities_oldcities j ON n.id = j.newcity_id
JOIN oldcities o ON o.id = j.oldcity_id
JOIN prefs p ON n.prefs_id = p.id
で、めでたくVIEWができたのでSQLをVIEWを使って書き直し。
SELECT
nid,
pref,
nc,
yomi,
gdate,
id oid,
city oc
FROM
(SELECT DISTINCT
nid,
pref,
nc,
yomi,
gdate
FROM city
WHERE some_condition
LIMIT offset, limit
) AS vn,
oldcities o,
joined j
WHERE vn.nid = j.newcity_id
AND o.id = j.oldcity_id
外側のJOINはWHERE句になったままですが、ま、いいや(^^;
んで一応これで動くことは動くんですが、このSQLが果たして“ちゃんとした？”モノなのかどうか結構不安だったのでネットをごそごそと調べてみると…。
まずFROM句のサブクエリに関してはMySQLのマニュアルでも“正式に使用できます”と書いてあったので一応ひと安心。
次にDISTINCTですが別に使用法は間違ってなさそうなのですが、ネットを検索するとDISTINCTは遅いとかGROUP BYに変えろとかいう意見もちらほらと。ですが最終的にDISTINCTとGROUP BY使いわけについてというスレッドを見て、DISTINCTのままいこうと決めました。
上記のスレッドではある方が、DISTINCTは遅いのでGROUP BYにすべきだ！という間違った考え方が流布されているようだが、そんなことはないということを力説されています。またこちらのBlogでもGROUP BYの方が多少は速いかもしれないが可読性の面からも重複行を削除する場合はDISTINCTの方がよいのではないか？とおっしゃられています。
というわけでSQLもすっきりしたので、次回ようやくフレームワークの話題へ…。（ってかいつになったらCodeIgniterの話書けるんだろう）
ラベル：SQL
