ＵＲＬで検索する場合はハッシュ化してインデックスをつけるべし

超恥ずかしい事実判明。
これまでブックマークサイトでＵＲＬ等を重複チェックするときに

where url like 'http://'とかってしてて
重くなってきたので
varcharにインデックス張れんのかなーとか
全文検索エンジンがいるのかなーとか
思ってたが(>>varcharは255文字までインデックス張れる)、
一般的にurl等はハッシュ化してテーブルに格納して、そっち側をキーにするのが定石らしい。

インデックスの肥大化を防ぐ為に
1. インデックスが大きすぎる文字列型よりは整数型や日付/時刻型
2. 先頭Nバイトだけもつ
3. 数値変換したインデックスをもつ
ということが重要だそうです！

またmd5やsha1ではなくCRC32というデータサイズの小さなハッシュを作り、
ＵＲＬと一緒に検索させる方法が良いらしい。
by
http://d.hatena.ne.jp/a666666/20100928/1285607257
http://bohebohe.livedoor.biz/archives/51723503.html#

select
url
from
bookmark
where 
url = 'http:/aaaaaaaaaaaa'

〇

select
url
from
bookmark
where 
url = 'http:/aaaaaaaaaaaa'　and url_hash = CRC32('http://aaaaaaaaaaaa');

で、さっそく既存のテーブルに変更を加える↓

#カラムを追加
alter table clip_bookmark add site_url_hash int(10) default 0;
Query OK, 287290 rows affected (4 min 34.80 sec)
#インデックス追加
alter table clip_bookmark add index idx_site_url_hash(site_url_hash);
Query OK, 287296 rows affected (2 min 10.81 sec)
#インデックス確認
SHOW INDEX FROM clip_bookmark;

#(2011.02.17 intだとオーバーフローのためbigintへ変更)
ALTER TABLE clip_bookmark MODIFY site_url_hash bigint DEFAULT 0;



#ＵＲＬからハッシュ値を生成するプロシージャー
delimiter //
drop procedure update_hash_url//
create procedure update_hash_url()
begin
    declare done int;
    declare cur cursor for
        select
            clip_bookmark_code,
            site_url,
            CRC32(site_url)
        from
            clip_bookmark
        where
            site_url_hash = 0
            limit 10000;
    declare exit handler for not found set done = 0;
    set done = 1;
    open cur;
    while done do
        fetch cur into
            local_clip_bookmark_code,
            local_site_url,
            local_site_url_hash
        ;
        update
            clip_bookmark
        set
            site_url_hash = local_site_url_hash
        where
            clip_bookmark_code = local_clip_bookmark_code
        ;
    end while;
    close cur;
end
//
delimiter ;
#プロシージャーを実行
call update_hash_url;

#あとはアプリケーション側の検索も諸々変更する
#完了