現在郵便番号のサジェスト処理で格闘中。
クライアント
↓
ajaxで4桁目を入れたら取得
↓
sqliteに格納されている郵便番号＆住所データをselect
↓
返す
↓
受け取って住所をリスト表示
今のシステムは前任者のものを引き継いだ形なので、何故郵便番号＆住所データをメインのDBではなく、sqliteでやってるかは不明。ファイルとして扱えるから便利だからかな？
まぁ読み込みにしか使わないからね。
メインDBに移すのも考えたが、他でも使ってるので、失敗したときのリスクがでかいし、改修箇所も多くなるなぁ、ということで、sqlite側に手を入れることに。
出来上がっているsqliteに入って、index貼ろうと思ったわけで。
sqlite3 postcode.db
SQL error: file is encrypted or is not a database
と怒られる。？？？？
これsqlite3のファイルでないの？
郵便番号DB（sqlite）を作成するphpのシェルを確認したところ、sqlite_openで作成していた。
phpのsqlite_openで作成すると、sqlite2になるんだよね・・・
それで怒られるわけか。
互換性とか持たせてくれててもいいのに・・・
開発環境にはsqlite2が無い。installして3と共存するようにするか？うーんめんどい。
じゃあいっそのことsqlite2ではなく、sqlite3でDB作るようにしちまうか？いや、これでプログラム側から読み込めないとかあったらまた改修範囲がでかくなる。
どっちもめんどいので、考えた。作成する時のphpのシェルで、続けてINDEXを貼っちまえばよくね？
テスト→できたっぽい。
さてさて、ではいよいよ改修に入ろう。
まずFirefoxのFirebugで、何桁目から取得し始めるのがいいか試した。
どうも4桁目がよさげ。
それより後ろだと、7桁目までを入力し終わる時間が短いので、取得して返ってくるのが1テンポ遅れる。
逆にそれより前だと、今度はデータが多すぎで待たされる。
ということで、4桁に標準を合わせた。
4桁目で取りに来たとすると、今のテーブルだと7桁の郵便番号フィールドに対してlikeで取得しにいくことになるが、likeは時間がかかる。
とはいってもINDEX貼ってみりゃ合格ラインにならんかな。
→貼ってやってみたら逆に遅くなったからビビった。
sqliteだとINDEX貼ってもlike検索ではINDEX使ってくれないらしい。
まぁlikeでも使えるようにする方法はあるんだが、
→例：CREATE TABLE t (c COLLATE NOCASE); のようにCOLLATE NOCASEを付ける
それより、4桁のフィールドを作った方がいいだろうという結論。
DB作成するシェルに、郵便番号のCSVを分解する部分で、7桁郵便番号から頭4桁を抽出して別フィールドに格納するようにした。7桁のフィールドはそのままだから、他で動いてる現行の処理には影響ない。
更に、その4桁フィールドにINDEXを貼る。
で、whereを、likeでなく完全一致で取りに行くように修正。
取得するsqlは、4桁なら4桁フィールドを、7桁なら7桁フィールドを、絶対指定で取りに行く、それ以外の桁は発生しない予定だが、まぁ一応Likeで動くようにだけしておこう。
めでたく取得速度が倍になりました。
ちなみにメインDBでindex貼って試してもみたんだが、チューニング後のsqliteと同等くらいだった。
sqliteあなどれんなぁ。
jqueryの方も読み込んだあとはcacheを使うとか、いろいろ細かい工夫を加えたところ、良い感じになったよ。
免責
この記事やプログラムによって生じた事故・損害などは一切保証致しません。ご自身の責任でご使用ください。
子育てブログ「おとう日記」はじめました。
興味ある方、是非ご覧下さい！
おとう日記
コピペプログラマの倉庫を作りました。
サンプルプログラムなど置いておきますのでお立ち寄り下さい。
コピペプログラマ倉庫
良ければ↓投票お願いします↓ m(._.)m ペコッ
人気ブログランキングへ
クライアント
↓
ajaxで4桁目を入れたら取得
↓
sqliteに格納されている郵便番号＆住所データをselect
↓
返す
↓
受け取って住所をリスト表示
今のシステムは前任者のものを引き継いだ形なので、何故郵便番号＆住所データをメインのDBではなく、sqliteでやってるかは不明。ファイルとして扱えるから便利だからかな？
まぁ読み込みにしか使わないからね。
メインDBに移すのも考えたが、他でも使ってるので、失敗したときのリスクがでかいし、改修箇所も多くなるなぁ、ということで、sqlite側に手を入れることに。
出来上がっているsqliteに入って、index貼ろうと思ったわけで。
sqlite3 postcode.db
で試しに「.tables」とかselectとかしてみても
SQL error: file is encrypted or is not a database
と怒られる。？？？？
これsqlite3のファイルでないの？
郵便番号DB（sqlite）を作成するphpのシェルを確認したところ、sqlite_openで作成していた。
phpのsqlite_openで作成すると、sqlite2になるんだよね・・・
それで怒られるわけか。
互換性とか持たせてくれててもいいのに・・・
開発環境にはsqlite2が無い。installして3と共存するようにするか？うーんめんどい。
じゃあいっそのことsqlite2ではなく、sqlite3でDB作るようにしちまうか？いや、これでプログラム側から読み込めないとかあったらまた改修範囲がでかくなる。
どっちもめんどいので、考えた。作成する時のphpのシェルで、続けてINDEXを貼っちまえばよくね？
テスト→できたっぽい。
さてさて、ではいよいよ改修に入ろう。
まずFirefoxのFirebugで、何桁目から取得し始めるのがいいか試した。
どうも4桁目がよさげ。
それより後ろだと、7桁目までを入力し終わる時間が短いので、取得して返ってくるのが1テンポ遅れる。
逆にそれより前だと、今度はデータが多すぎで待たされる。
ということで、4桁に標準を合わせた。
4桁目で取りに来たとすると、今のテーブルだと7桁の郵便番号フィールドに対してlikeで取得しにいくことになるが、likeは時間がかかる。
とはいってもINDEX貼ってみりゃ合格ラインにならんかな。
→貼ってやってみたら逆に遅くなったからビビった。
sqliteだとINDEX貼ってもlike検索ではINDEX使ってくれないらしい。
まぁlikeでも使えるようにする方法はあるんだが、
→例：CREATE TABLE t (c COLLATE NOCASE); のようにCOLLATE NOCASEを付ける
それより、4桁のフィールドを作った方がいいだろうという結論。
DB作成するシェルに、郵便番号のCSVを分解する部分で、7桁郵便番号から頭4桁を抽出して別フィールドに格納するようにした。7桁のフィールドはそのままだから、他で動いてる現行の処理には影響ない。
更に、その4桁フィールドにINDEXを貼る。
で、whereを、likeでなく完全一致で取りに行くように修正。
取得するsqlは、4桁なら4桁フィールドを、7桁なら7桁フィールドを、絶対指定で取りに行く、それ以外の桁は発生しない予定だが、まぁ一応Likeで動くようにだけしておこう。
めでたく取得速度が倍になりました。
ちなみにメインDBでindex貼って試してもみたんだが、チューニング後のsqliteと同等くらいだった。
sqliteあなどれんなぁ。
jqueryの方も読み込んだあとはcacheを使うとか、いろいろ細かい工夫を加えたところ、良い感じになったよ。
免責
この記事やプログラムによって生じた事故・損害などは一切保証致しません。ご自身の責任でご使用ください。
子育てブログ「おとう日記」はじめました。
興味ある方、是非ご覧下さい！
おとう日記
コピペプログラマの倉庫を作りました。
サンプルプログラムなど置いておきますのでお立ち寄り下さい。
コピペプログラマ倉庫
良ければ↓投票お願いします↓ m(._.)m ペコッ
人気ブログランキングへ