Movable Typeで検索がとっても遅い場合は
エントリーが増えると、異様に検索速度が遅くなるMovable Type
/usr/local/mysql/bin/mysqld, Version: 5.0.42-enterprise-gpl-log (MySQL Enterprise Server (GPL)). started with:
Tcp port: 3309 Unix socket: /tmp/mysql4.sock
Time Id Command Argument
070719 18:23:47 1 Connect mt@localhost on mt4
1 Query set autocommit=1
1 Query SELECT config_id
FROM mt_config
1 Query SELECT config_id, config_data
FROM mt_config
WHERE (config_id IN ('1'))
1 Query show variables like "character_set_database"
1 Query SET NAMES utf8
1 Query SELECT author_id
FROM mt_author
WHERE (author_name = 'hirohama') AND (author_type = '1')
1 Query SELECT author_id, author_api_password, author_can_create_blog, author_can_view_log, author_created_by, author_created_on, author_email, author_entry_prefs, author_external_id, author_hint, author_is_superuser, author_meta, author_modified_by, author_modified_on, author_name, author_nickname, author_password, author_preferred_language, author_public_key, author_remote_auth_token, author_remote_auth_username, author_status, author_text_format, author_type, author_url
FROM mt_author
WHERE (author_id IN ('1'))
1 Query SELECT session_id
FROM mt_session
WHERE (session_kind = 'US') AND (session_id = 'xxxxx')
1 Query SELECT session_id, session_data, session_email, session_kind, session_name, session_start
FROM mt_session
WHERE (session_id IN ('xxxxx'))
1 Query SELECT session_id
FROM mt_session
WHERE (session_kind = 'UA') AND (session_name = '1')
1 Query SELECT session_id, session_data, session_email, session_kind, session_name, session_start
FROM mt_session
WHERE (session_id IN ('ooooo'))
1 Query SELECT 1 FROM mt_session
WHERE (session_id = 'yyyyy')
1 Query UPDATE mt_session SET
session_start = 'zzzzz'
WHERE (session_id = 'yyyyy')
1 Query SELECT blog_id, blog_allow_anon_comments, blog_allow_comment_html, blog_allow_commenter_regist, blog_allow_comments_default, blog_allow_pings, blog_allow_pings_default, blog_allow_reg_comments, blog_allow_unreg_comments, blog_archive_path, blog_archive_tmpl_category, blog_archive_tmpl_daily, blog_archive_tmpl_individual, blog_archive_tmpl_monthly, blog_archive_tmpl_weekly, blog_archive_type, blog_archive_type_preferred, blog_archive_url, blog_autodiscover_links, blog_autolink_urls, blog_basename_limit, blog_cc_license, blog_children_modified_on, blog_convert_paras, blog_convert_paras_comments, blog_created_by, blog_created_on, blog_custom_dynamic_templates, blog_days_on_index, blog_description, blog_email_new_comments, blog_email_new_pings, blog_entries_on_index, blog_file_extension, blog_google_api_key, blog_internal_autodiscovery, blog_is_dynamic, blog_junk_folder_expiry, blog_junk_score_threshold, blog_language, blog_manual_approve_commenters, blog_meta, blog_moderate_pings, blog_moderate_unreg_comments, blog_modified_by, blog_modified_on, blog_mt_update_key, blog_name, blog_old_style_archive_links, blog_ping_blogs, blog_ping_google, blog_ping_others, blog_ping_technorati, blog_ping_weblogs, blog_remote_auth_token, blog_require_comment_emails, blog_sanitize_spec, blog_server_offset, blog_site_path, blog_site_url, blog_sort_order_comments, blog_sort_order_posts, blog_status_default, blog_use_comment_confirmation, blog_welcome_msg, blog_words_in_excerpt
FROM mt_blog
WHERE (blog_id = '1')
LIMIT 1
1 Query INSERT INTO mt_log
(log_author_id, log_blog_id, log_category, log_class, log_created_by, log_created_on, log_ip, log_level, log_message, log_metadata, log_modified_by, log_modified_on)
VALUES ('1', '1', 'straight_search', 'search', '1', '2007-07-19 09:23:47', '127.0.0.1', '1', '検索: myisam', NULL, NULL, '2007-07-19 09:23:47')
1 Query SELECT entry_id
FROM mt_entry
WHERE (entry_status = '2') AND (entry_blog_id IN ('1'))
ORDER BY entry_authored_on DESC
1 Query SELECT entry_id, entry_allow_comments, entry_allow_pings, entry_atom_id, entry_author_id, entry_authored_on, entry_basename, entry_blog_id, entry_category_id, entry_class, entry_convert_breaks, entry_created_by, entry_created_on, entry_excerpt, entry_keywords, entry_modified_by, entry_modified_on, entry_pinged_urls, entry_status, entry_tangent_cache, entry_template_id, entry_text, entry_text_more, entry_title, entry_to_ping_urls, entry_week_number
FROM mt_entry
WHERE (entry_id IN ('48','47','46','45','44','43','42','40','39','38','37','36','35','34','33','32','31','30','29','28','27','26','25','24','22','21','20','19','18','17','15','14','13','12','11','10','9','8','7','5','4','3'))
070719 18:23:48 1 Query SELECT template_id
FROM mt_template
WHERE (template_blog_id = '1') AND (template_type = 'search_results')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('15'))
1 Query SELECT template_id
FROM mt_template
WHERE (template_name = 'Header') AND (template_type = 'custom') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('28'))
1 Query SELECT template_id
FROM mt_template
WHERE (template_identifier = 'base_stylesheet') AND (template_type = 'index') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('5'))
1 Query SELECT template_id
FROM mt_template
WHERE (template_identifier = 'theme_stylesheet') AND (template_type = 'index') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('1'))
1 Query SELECT template_id
FROM mt_template
WHERE (template_identifier = 'hirohama_stylesheet') AND (template_type = 'index') AND (template_blog_id = '1')
1 Query SELECT template_id
FROM mt_template
WHERE (template_name = 'hirohama_stylesheet') AND (template_type = 'index') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('59'))
1 Query SELECT template_id
FROM mt_template
WHERE (template_name = 'Entry Summary') AND (template_type = 'custom') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('19'))
1 Query SELECT templatemap_id
FROM mt_templatemap
WHERE (templatemap_is_preferred = '1') AND (templatemap_archive_type = 'Individual') AND (templatemap_blog_id = '1')
1 Query SELECT templatemap_id, templatemap_archive_type, templatemap_blog_id, templatemap_file_template, templatemap_is_preferred, templatemap_template_id
FROM mt_templatemap
WHERE (templatemap_id IN ('6'))
1 Query SELECT template_id
FROM mt_template
WHERE (template_name = 'Entry Metadata') AND (template_type = 'custom') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('25'))
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '46')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '46')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('45'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '45')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '43')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '43')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('42'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '42')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '40')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '40')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('39'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '39')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '38')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '38')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('37'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '37')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '35')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '35')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('34'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '34')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '30')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '30')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('29'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '29')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '27')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '27')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('26'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '26')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '26')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '26')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('25'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '25')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '19')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '19')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('18'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '18')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '10')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '10')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('9'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '9')
1 Query SELECT COUNT(*)
FROM mt_comment
WHERE (comment_visible = '1') AND (comment_entry_id = '8')
1 Query SELECT trackback_id
FROM mt_trackback
WHERE (trackback_entry_id = '8')
1 Query SELECT trackback_id, trackback_blog_id, trackback_category_id, trackback_created_by, trackback_created_on, trackback_description, trackback_entry_id, trackback_is_disabled, trackback_modified_by, trackback_modified_on, trackback_passphrase, trackback_rss_file, trackback_title, trackback_url
FROM mt_trackback
WHERE (trackback_id IN ('7'))
1 Query SELECT COUNT(*)
FROM mt_tbping
WHERE (tbping_visible = '1') AND (tbping_tb_id = '7')
1 Query SELECT template_id
FROM mt_template
WHERE (template_name = 'Footer') AND (template_type = 'custom') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('22'))
1 Query SELECT template_id
FROM mt_template
WHERE (template_name = 'Sidebar') AND (template_type = 'custom') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('24'))
1 Query SELECT DISTINCT tag_id, tag_name
FROM mt_tag, mt_objecttag
WHERE (tag_is_private = '0') AND (objecttag_blog_id IN ('1')) AND (objecttag_object_datasource = 'entry') AND (tag_id = objecttag_tag_id)
ORDER BY tag_name ASC
1 Query SELECT tag_id, tag_is_private, tag_n8d_id, tag_name
FROM mt_tag
WHERE (tag_id IN ('8','16','6','13','10','11','17'))
1 Query SELECT COUNT(*), objecttag_tag_id
FROM mt_entry, mt_objecttag
WHERE (entry_status = '2') AND (entry_class = 'entry') AND (entry_blog_id IN ('1')) AND (objecttag_blog_id IN ('1')) AND (objecttag_object_datasource = 'entry') AND (entry_id = objecttag_object_id)
GROUP BY objecttag_tag_id
1 Query SELECT template_id
FROM mt_template
WHERE (template_identifier = 'atom') AND (template_type = 'index') AND (template_blog_id = '1')
1 Query SELECT template_id, template_blog_id, template_build_dynamic, template_created_by, template_created_on, template_identifier, template_linked_file, template_linked_file_mtime, template_linked_file_size, template_meta, template_modified_by, template_modified_on, template_name, template_outfile, template_rebuild_me, template_text, template_type
FROM mt_template
WHERE (template_id IN ('7'))
1 Quit
予想に反してSELECT ...WHERE LIKE...が見つからないので、いったんアプリ側に該当部分を引っ張ってきて、PERLで文字列比較しているのでしょう。
もしそうだとすると、MySQL+Senna入れて、クエリをちょっと書き直して終わり、と簡単にはいかなそう。
1000件くらいのデータだったら、部分一致検索でも、絶対現状よりも早い。
おそらく、ボトルネックはMySQL Serverでは無いので、以下。
案１：すぐに思いつくのは、Google用にsitemap作成して、ちゃんと巡回してもらって、Googleのサイト内検索を利用する方法。
他力本願だけど、一番簡単で一番確実。
Google用sitemapは、作成済み、登録済みだったので、このページの右上にGoogleサイト内検索を一時的につけてみました。
ちなみに、Google Co-opでもっとさりげなく検索窓を付ける事ができるらしい。
Movable Type の検索を Google Co-op に切り替え：Jay's Room
案２：mt-search.cgiの代わりにPHPを作成して、MySQL Serverを直接検索しにいかせる。
可能であればMySQL+Sennaで。
（mt-search.cgiを使う）「タグ」や「タグクラウド」などを利用していないのであれば、十分考慮に値するかと。
案３：今日のMovable Type：Ajaxを利用したMT用超高速検索システムはスゴすぎる！: 世界中の１％の人々へ
こんなエントリーを発見。
はやい！やはすぎる！！！
案４：WiSE MT: ブログ検索パッケージ | WiSE | 製品情報
有料だと、こんなのもあるらしいけど、お金払わなくてもなんとかなりそう。
結構FAQのようで、皆さんイロイロ工夫しているようです。
ちゃんと調べると、回答は見つかりそうです。
案５：mt-search.cgiをmod_cacheで超高速化する!! - Ogawa::Memoranda：ベンチマークでキャッシュの有効性を確認していて、キャッシュを有効にするとかなり高速化するらしい。
これは十分考慮に値しそうじゃない？
と思ったらこんなチェンジログもあるので、4.0ではなおっているカモ知れない。
私が借りているホスティング環境では、それほどはやく無かったけれども。
movabletype.org: Movable Type 4.0 Beta Change Log
47890: Apply Search.pm utf-8 patch by Ben
FIXED: Users reported from way back in MT3.x that public blog search was slow. This resulted from a UTF-8 encoding problem that has now been fixed.