質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.11%

wordpress カスタムフィールド検索が遅い

受付中

回答 2

投稿 編集

  • 評価
  • クリップ 2
  • VIEW 3,751

SSaka

score 92

前提・実現したいこと

wordpress 4.5.2でカスタムフィールドで作成した記事が数万件あります。
このカスタムフィールドを
1.カスタムフィールドの名前順ソート
2.カスタムフィールドのあるなし判定
という条件で検索したいと考えています。

meta_queryで検索自体は実現したのですが、
データ量のせいか、検索に50秒ほどかかってしまい、実用に耐えない状況です。

Smart Custom Fieldを利用してカスタムフィールドを定義・データを入れているため、
post_metaにデータが入っていることまではわかったのですが、
インデックスの作成などチューニング手法で良い方法に出会えていません

発生している問題・エラーメッセージ

meta_queryで検索自体は実現したのですが、
データ量のせいか、検索に50秒ほどかかってしまい、実用に耐えない状況です。

該当のソースコード

$keyWords = $_GET['s'];
        $post_type = 'shop_data';
        $searchArea = $_GET['searchArea'];
        $searchCity = $_GET['searchCity'];

        if($searchArea) {
            $taxquery[] = array(
                'taxonomy' => 'shop_area',
                'terms' => $searchArea,
                'field' => 'slug',
                'operator' => 'AND'
            );
        }

        $taxquery['relation'] = 'AND';

        if($searchCity) {
            $metaquery[] = array(
                'key' => 'shop_city',
                'value' => $searchCity,
                'compare' => 'LIKE',
                'operator' => 'OR'
            );
        }
        if( mb_strlen($keyWords)>0 ){
            $keyWordQuery[] = array(
                'key' => 'shop_name',
                'value' => mb_convert_kana($keyWords,'rsn'),
                'compare' => 'LIKE',
                'operator' => 'OR'
            );
            $keyWordQuery[] = array(
                'key' => 'shop_name',
                'value' => mb_convert_kana($keyWords,'RSN'),
                'compare' => 'LIKE',
                'operator' => 'OR'
            );
            $keyWordQuery[] = array(
                'key' => 'shop_name',
                'value' => mb_strtolower(mb_convert_kana($keyWords,'rsn')),
                'compare' => 'LIKE',
                'operator' => 'OR'
            );
            $keyWordQuery[] = array(
                'key' => 'shop_name',
                'value' => mb_strtolower(mb_convert_kana($keyWords,'RSN')),
                'compare' => 'LIKE',
                'operator' => 'OR'
            );
            $keyWordQuery[] = array(
                'key' => 'shop_name',
                'value' => mb_strtoupper(mb_convert_kana($keyWords,'rsn')),
                'compare' => 'LIKE',
                'operator' => 'OR'
            );
            $keyWordQuery[] = array(
                'key' => 'shop_name',
                'value' => mb_strtoupper(mb_convert_kana($keyWords,'RSN')),
                'compare' => 'LIKE',
                'operator' => 'OR'
            );
            $keyWordQuery[] = array(
                'key' => 'shop_id',
                'value' => mb_convert_kana($keyWords,'n'),
                'compare' => '=',
            );
            $keyWordQuery['relation'] = 'OR';
            $metaquery[] = $keyWordQuery;
        }
        $metaquery['relation'] = 'AND';

        $machine_meta[] = array(
            'key' => 'p_data',
            'value' => null,
            'compare' => '!='
        );
        $machine_meta[] = array(
            'key' => 's_data',
            'value' => null,
            'compare' => '!='
        );
        $machine_meta['relation'] = "OR";

        // p台データなし共通クエリ
        $no_p_meta[] = array(
            'key' => 'p_data',
            'value' => null,
            'compare' => '='
        );
        $no_p_meta[] = array(
            'key' => 'p_data',
            'compare' => 'NOT EXISTS'
        );
        $no_p_meta['relation'] = "OR";

        // s台データなし共通クエリ
        $no_s_meta[] = array(
            'key' => 's_data',
            'value' => null,
            'compare' => '='
        );
        $no_s_meta[] = array(
            'key' => 's_data',
            'compare' => 'NOT EXISTS'
        );
        $no_s_meta['relation'] = "OR";

        // 台データなし共通クエリ
        $no_machine_meta[] = $no_p_meta;
        $no_machine_meta[] = $no_s_meta;
        $no_machine_meta['relation'] = "AND";

        // 取材IDあり
        $report_meta = array(
            'key' => 'report_exists',
            'value' => true,
            'type' => 'BOOLEAN',
            'compare' => "="
        );

        // 取材IDなし
        $no_report_meta[] = array(
            'key' => 'report_exists',
            'value' => false,
            'type' => 'BOOLEAN',
            'compare' => "="
        );
        $no_report_meta[] = array(
            'key' => 'report_exists',
            'value' => null,
            'compare' => '='
        );
        $no_report_meta[] = array(
            'key' => 'report_exists',
            'compare' => 'NOT EXISTS'
        );
        $no_report_meta['relation'] = "OR";

        // 1.台データURLあり/取材IDあり
        $query1_meta = $metaquery;
        $query1_meta[] = $machine_meta;
        $query1_meta[] = $report_meta;
        $query1 = new WP_Query (array(
            'post_type' => $post_type,
            'tax_query' => $taxquery,
            'meta_query' => $query1_meta,
            'posts_per_page' => -1,
            'meta_key' => 'shop_name',
            'orderby' => 'meta_value',
            'order' => 'ASC'
            ));

        // 2.台データURLなし/取材IDあり
        $query2_meta = $metaquery;
        $query2_meta[] = $no_machine_meta;
        $query2_meta[] = $report_meta;
        $query2 = new WP_Query (array(
            'post_type' => $post_type,
            'tax_query' => $taxquery,
            'meta_query' => $query2_meta,
            'posts_per_page' => -1,
            'meta_key' => 'shop_name',
            'orderby' => 'meta_value',
            'order' => 'ASC'
            ));

        // 3.台データURLあり/取材IDなし
        $query3_meta = $metaquery;
        $query3_meta[] = $machine_meta;
        $query3_meta[] = $no_report_meta;
        $query3 = new WP_Query (array(
            'post_type' => $post_type,
            'tax_query' => $taxquery,
            'meta_query' => $query3_meta,
            'posts_per_page' => -1,
            'meta_key' => 'shop_name',
            'orderby' => 'meta_value',
            'order' => 'ASC'
            ));

        // 4.台データURLなし/取材IDなし
        $query4_meta = $metaquery;
        $query4_meta[] = $no_machine_meta;
        $query4_meta[] = $no_report_meta;
        $query4 = new WP_Query (array(
            'post_type' => $post_type,
            'tax_query' => $taxquery,
            'meta_query' => $query4_meta,
            'posts_per_page' => -1,
            'meta_key' => 'shop_name',
            'orderby' => 'meta_value',
            'order' => 'ASC'
            ));

試したこと

  1. indexの作成
    mysql> create index x_post_title ON wp_postmeta(post_id,meta_key(64),meta_value(64));

補足情報(言語/FW/ツール等のバージョンなど)

PHP 5.3.3
MySQL 5.1.73
WordPress 4.5.2

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

+2

MySQLのチューニングの前に念のためWordPressで行なっている4回のWP_Query($query1〜$query4)が全部遅いのか、特定の条件の場合にくなっているのか、これらを含む前後のコードも併せてmicrotime()などでそれぞれの実行時間を出してみてはいかがでしょうか。(すべて遅いような気もしますが。。)

[デバッグ]PHPで処理の時間を計測するための方法

その上で、遅い処理に対してのMySQLのチューニングを

  1. クエリログなどから実際にWordPressがデータの検索時に発行しているクエリを確認
  2. EXPLAINなどで重いクエリの原因を調査
  3. 原因となっている部分をイデックスの作成などで改善

といった流れで行なっていくのが良いかと思います。

お使いの環境によってはそこまでいじれないよ、ということもあるかと思いますが、下記のような方法でスロークエリがわかるようにしておくより話が早いかもしれません。

MySQL 5.1のスロークエリログ

WordPress全体をチューニングしていく際には、下記のような記事が参考になるかもしれません。

WordPressを100倍速くする! MySQLの調整やnginx proxy cache

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/25 12:53

    EXPLAIN結果の読み方としては、各行のExtraで、Using indexが含まれていれば効果的にインデックスから読まれているということでしょうか。

    たとえば足りないIndexを探す場合、using indxの無い行を全てusing indexとする方向で試して、その組合せの結果、1個目のusing temporary / using filesortが消えるのではないかな?という推測を考えています

    キャンセル

  • 2016/06/25 15:10

    出先のため確認が遅くなりすいません。

    >> EXPLAIN結果の読み方としては、各行のExtraで、Using indexが含まれていれば効果的にインデックスから読まれているということでしょうか。

    そうですね。Using indexはインデックスから問い合わせ結果を返せているということです。

    >> たとえば足りないIndexを探す場合、using indxの無い行を全てusing indexとする方向で試して、その組合せの結果、1個目のusing temporary / using filesortが消えるのではないか

    たとえばクエリを

    ----
    explain
    SELECT wp_posts.*
    FROM wp_posts
    WHERE wp_posts.post_type = 'shop_data'
    AND (wp_posts.post_status = 'publish')
    GROUP BY wp_posts.ID
    ----

    まで削っても恐らくusing temporary / using filesortになるのではないでしょうか。
    (ちゃんと確認できているわけではないため見当違いでしたらすいません…。)
    パッと拝見したところですと、explainの|1|wp_posts|以外はそこまで大きな問題を抱えていないようにも見受けられますので、やはりこのwp_postsに対する問い合わせをどうにかできれば良いのではないかと思います。

    先程コメントいたしました「idx_type_status,idx_type,idx_status」についてはちょっと僕も勘違いをしており、まったく意味がなかったですね。申し訳ないです(消してしまっても問題ありません)。
    別案として同じようなインデックスですが、wp_postsに(post_type, ID) と (post_status, ID) のインデックスを貼り、この2つのインデックスを結合してうまいこと結果を返してくれれば少し早くなるかも?と思いました。

    上記のようにクエリを削っていった際に、どこかでガツンと時間がかかるようになれば、そこが原因、と捉えることもできますので、JOINやWHEREの条件を少しずつ削ってみてどうかも試されてみると良いかと思います。

    キャンセル

  • 2016/06/25 15:55

    query1・・・0.1秒以下
    query2・・・0.1秒以下
    query3・・・0.1秒以下
    query4・・・54秒
    となっていて、後半は1,2,3で検索した結果以外のものを対象としていました。
    1,2,3のpost_idを配列$distinct_post_idsに格納して、
    query4のmeta_query削除
    WP_Queryに、'post__not_in' => $distinct_post_ids,を入れることで、
    NOT IN post_id = 記事番号,記事番号,記事番号というイメージで
    除外+54秒かかったクエリを0.1秒以下にすることができまし。

    ひとまずは前に進んだのですが、今後のためにもチューニングは引続き試してみたいと思います。

    頂いた、SQLを元に検索しても、やはりusing filesortは消えませんでした。
    slow queryのSQLをみてleft joinの多さに知識ないなりにもびっくりした面もあるので、
    もうすこしシンプルなSQLで、このあたりを解消できるSQL+filesortをさせないというところを念頭において試してみます。

    キャンセル

+2

DBのチューニングは不得意なので、私に出来る部分のみ指摘させていただきます。

まず、$query1から$query4までの結果セットの大部分は重複していることです。
取材IDの有無×台データ有無の4通り、ということは、表示の側で分岐すればDBへの問い合わせを3回減らすことが出来ます。表示側で4つに分ける処理はDBへの問い合わせよりは早いのではと思います。(予想なので、計測してみてください)

$query0_meta = $metaquery;
$query0 = new WP_Query (array(
    'post_type' => $post_type,
    'tax_query' => $taxquery,
    'meta_query' => $query0_meta,
    'posts_per_page' => -1,
    'meta_key' => 'shop_name',
    'orderby' => 'meta_value',
    'order' => 'ASC'
    ));

また、このクエリを取得する部分がどこに書かれているのか提示されていませんが、もし固定ページ/検索ページのテンプレート上に書かれている場合は、ページ自体のWP_Queryが発行された後になります。
ページ自体のWP_Queryの発行時に pre_get_posts フィルタでご自身のクエリに書き換えることで、DBへの問い合わせを1回減らすことが出来ます。

【WordPressの新・旧ループからカスタムクエリ・アーカイブまで徹底解説 | OXY NOTES】
http://oxynotes.com/?p=8615

あとは、DBのキャッシュや表示のキャッシュをとることでも表示の高速化を図れると思います。(キャッシュの生成/破棄タイミングが案外難しいですが)

【W3 Total Cache -? WordPress Plugins】
https://wordpress.org/plugins/w3-total-cache/

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/06/25 16:11

    こんにちは。いつもお世話になりっぱなしです。ありがとうございます。

    query1~query4の問い合わせの重複は自分も非常に気になっていました。
    主に、この「台データ」、「取材ID」の部分はmetaデータなため、
    smart custom fieldの関数でwhileする度に再度カスタムフィールドを変数に再問い合わせするような構造にしてしまっていて、3分以上応答がなくなってしまうことがあり、
    クエリ側で除外してみよう、と試した結果です。

    query0の変数だけで再問い合わせ無くmeta_key+meta_valueを取得分岐することができれば、表示側で負荷を増やさずに分岐できそうです。
    とくに悩んだところは、表示優先順位で、
    shop_nameのあいうえお順ソートを前提に、
    query1,2,3の順番に結果を表示、それ以外はsort4という感じにしたいというとこでした。

    search.phpにおいているのですが、ページ自体が、1回クエリを発行していてさらに、WP_Queryしているというところは構造的に自分で理解できていなかった点でした。

    DBのキャッシュも考えていましたが的確なプラグインに出会えていませんでした。
    プラグインをためしつつもう少し検討してみます。

    キャンセル

  • 2016/06/25 17:22

    > 再度カスタムフィールドを変数に再問い合わせするような構造にしてしまっていて

    すいません、その点を見落としていました。確かにDBへの問い合わせが発生しますね。カテゴリやタグも確認しましたが、全て取得時にDBへの問い合わせが発生します。
    結局4つのクエリを投げるのが一番低コストですね…。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.11%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる