件数が増えると正規表現が圧倒的に遅くなってるな。
LIKE検索
'content__icontains'
正規表現
'content__iregex'
ENGINE=InnoDB DEFAULT CHARSET=utf8
+-------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+----------------+
| content | longtext | YES | | NULL | |
+-------------------------+--------------+------+-----+---------+----------------+
5.4 KBで116行のデータ保存し検出する行は20行目
---------------------------------------
65041件のテーブルから61702件検索
---------------------------------------
search str:MySQL server version
search_db_like_flg : True
2013-07-01 18:35:50.215440
61702件
2013-07-01 18:35:54.786196
4秒
search str:MySQL server version
search_db_like_flg : False
2013-07-01 18:36:27.829811
61702件
2013-07-01 18:37:26.619358
59秒
---------------------------------------
65041件のテーブルから1件SELECT
---------------------------------------
search str:SUBARU IMPREZA
search_db_like_flg : True
2013-07-01 19:10:01.690396
1件
2013-07-01 19:10:06.635353
5秒
search str:SUBARU IMPREZA
search_db_like_flg : False
2013-07-01 18:51:51.615525
1件
2013-07-01 18:52:49.288095
58秒
1万件づつ増やして(途中で失敗して厳密じゃないけど)SELECTした結果
---------------------------------------
MySQL server version
search_db_like_flg : True
2013-07-01 18:15:55.731895
10027件
2013-07-01 18:15:56.669266
MySQL server version
search_db_like_flg : False
2013-07-01 18:15:16.723236
10027件
2013-07-01 18:15:27.476034
---------------------------------------
MySQL server version
search_db_like_flg : True
2013-07-01 18:17:18.013135
20026件
2013-07-01 18:17:19.658371
MySQL server version
search_db_like_flg : False
2013-07-01 18:18:08.309758
21706件
2013-07-01 18:18:30.078852
---------------------------------------
MySQL server version
search_db_like_flg : True
2013-07-01 18:28:23.484440
31705件
2013-07-01 18:28:25.772512
MySQL server version
search_db_like_flg : False
2013-07-01 18:28:47.537359
31705件
2013-07-01 18:29:18.857616
---------------------------------------
MySQL server version
search_db_like_flg : True
2013-07-01 18:30:27.338600
41704件
2013-07-01 18:30:30.336758
MySQL server version
search_db_like_flg : False
2013-07-01 18:31:26.043456
41704件
2013-07-01 18:32:06.448256
---------------------------------------
MySQL server version
search_db_like_flg : True
2013-07-01 18:34:14.960486
51703件
2013-07-01 18:34:18.681043
MySQL server version
search_db_like_flg : False
2013-07-01 18:33:00.604202
51703件
2013-07-01 18:33:49.882839