さて、いろいろ考えてみたものの、なかなか速度が改善しないSQLデータベース検索だが、そもそも、レコード数を前もって間引いとけばいいんじゃない？というアイデアが出た。
つまり、現在の検索の順序として
- Variant countでカットオフ
- Gene countを計算し、recessiveかdominantかを分離
- サンプル名や遺伝子名で抽出
- その他カラムに含まれる情報で抽出
という流れだが、このうち１番目のカットオフを済ませたサブテーブルを一旦生成し、検索条件を入れ替えても2番目からの抽出で済ませるとぐっと速くなるのではないかと。
トータルで現在520検体分のAnnovarデータが有って、165万レコードが登録されているが、Variant countカットオフを例えば50に設定してやると14万レコードほどの検索となる。単純に1割以下のレコードから検索することになるわけで、コレはなかなか期待できる。
というわけで、現在のメインテーブルからVariant countの閾値を入力値以下だけ集めたサブテーブルを生成するページを設計してみた。
結果はとてもサクサク、とまでは行かないものの、普通にGoogleで検索をかけて結果が出てくる程度の速度で、クエリ結果を表示できた。
サブテーブルのカットオフは任意で設定できるようにフォーム入力をつけてみた。
これで、まずサブテーブルを生成し、その後検索作業をするという方式が出来上がったが、問題点もある。動的にサブテーブルを生成できる代わりに複数人が同じサブテーブルにアクセスすると、いつのまにか他の人がカットオフの閾値を変更したテーブルに書き換えちゃってた、なんて事態が起こりうるわけだ。
しかし逆に言うと、各ユーザーごとにサブテーブルと作業用ページをあてがってしまえば、各自好きなようにテーブルをカスタマイズして使ってもらうことも可能だと言うことだ。
各自専用テーブルおよび作業ページも自動で作成できれば完璧だが、それはちょっとハードルが高いかもしれない。
まずは雛形からのコピーもしくは10ユーザー程度のページを予め作っておいて、使用者に割り当てて使ってもらう、というのが容易かもしれない。
この方式で行くなら、以前試作してみた検索条件の保存などもユーザーごとに前もって用意することで実現できるかもしれない。
また、そもそもサブテーブルに必要なサンプルの分だけ読み込んで、そこだけで検索すればもっと高速に検索できそうな気もする。
このあたりは実際に使ってみて問題がないか確認する必要がありそうだ。
なお、各種フラグの挿入やvariant countの計算などは大本のテーブルに対して実施しておく必要があるので、この作業にはやはり時間がかかるのは仕方のないところである。
