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

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

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

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

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

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

###該当のソースコード

PHP
1		$keyWords = $_GET['s'];
2		$post_type = 'shop_data';
3		$searchArea = $_GET['searchArea'];
4		$searchCity = $_GET['searchCity'];
5
6		if($searchArea) {
7			$taxquery[] = array(
8				'taxonomy' => 'shop_area',
9				'terms' => $searchArea,
10				'field' => 'slug',
11				'operator' => 'AND'
12			);
13		}
14
15		$taxquery['relation'] = 'AND';
16
17		if($searchCity) {
18			$metaquery[] = array(
19				'key' => 'shop_city',
20				'value' => $searchCity,
21				'compare' => 'LIKE',
22				'operator' => 'OR'
23			);
24		}
25		if( mb_strlen($keyWords)>0 ){
26			$keyWordQuery[] = array(
27				'key' => 'shop_name',
28				'value' => mb_convert_kana($keyWords,'rsn'),
29				'compare' => 'LIKE',
30				'operator' => 'OR'
31			);
32			$keyWordQuery[] = array(
33				'key' => 'shop_name',
34				'value' => mb_convert_kana($keyWords,'RSN'),
35				'compare' => 'LIKE',
36				'operator' => 'OR'
37			);
38			$keyWordQuery[] = array(
39				'key' => 'shop_name',
40				'value' => mb_strtolower(mb_convert_kana($keyWords,'rsn')),
41				'compare' => 'LIKE',
42				'operator' => 'OR'
43			);
44			$keyWordQuery[] = array(
45				'key' => 'shop_name',
46				'value' => mb_strtolower(mb_convert_kana($keyWords,'RSN')),
47				'compare' => 'LIKE',
48				'operator' => 'OR'
49			);
50			$keyWordQuery[] = array(
51				'key' => 'shop_name',
52				'value' => mb_strtoupper(mb_convert_kana($keyWords,'rsn')),
53				'compare' => 'LIKE',
54				'operator' => 'OR'
55			);
56			$keyWordQuery[] = array(
57				'key' => 'shop_name',
58				'value' => mb_strtoupper(mb_convert_kana($keyWords,'RSN')),
59				'compare' => 'LIKE',
60				'operator' => 'OR'
61			);
62			$keyWordQuery[] = array(
63				'key' => 'shop_id',
64				'value' => mb_convert_kana($keyWords,'n'),
65				'compare' => '=',
66			);
67			$keyWordQuery['relation'] = 'OR';
68			$metaquery[] = $keyWordQuery;
69		}
70		$metaquery['relation'] = 'AND';
71		
72		$machine_meta[] = array(
73			'key' => 'p_data',
74			'value' => null,
75			'compare' => '!='
76		);
77		$machine_meta[] = array(
78			'key' => 's_data',
79			'value' => null,
80			'compare' => '!='
81		);
82		$machine_meta['relation'] = "OR";
83
84		// p台データなし共通クエリ
85		$no_p_meta[] = array(
86			'key' => 'p_data',
87			'value' => null,
88			'compare' => '='
89		);
90		$no_p_meta[] = array(
91			'key' => 'p_data',
92			'compare' => 'NOT EXISTS'
93		);
94		$no_p_meta['relation'] = "OR";
95
96		// s台データなし共通クエリ
97		$no_s_meta[] = array(
98			'key' => 's_data',
99			'value' => null,
100			'compare' => '='
101		);
102		$no_s_meta[] = array(
103			'key' => 's_data',
104			'compare' => 'NOT EXISTS'
105		);
106		$no_s_meta['relation'] = "OR";
107
108		// 台データなし共通クエリ
109		$no_machine_meta[] = $no_p_meta;
110		$no_machine_meta[] = $no_s_meta;
111		$no_machine_meta['relation'] = "AND";
112
113		// 取材IDあり
114		$report_meta = array(
115			'key' => 'report_exists',
116			'value' => true,
117			'type' => 'BOOLEAN',
118			'compare' => "="
119		);
120
121		// 取材IDなし
122		$no_report_meta[] = array(
123			'key' => 'report_exists',
124			'value' => false,
125			'type' => 'BOOLEAN',
126			'compare' => "="
127		);
128		$no_report_meta[] = array(
129			'key' => 'report_exists',
130			'value' => null,
131			'compare' => '='
132		);
133		$no_report_meta[] = array(
134			'key' => 'report_exists',
135			'compare' => 'NOT EXISTS'
136		);
137		$no_report_meta['relation'] = "OR";
138
139		// 1.台データURLあり/取材IDあり
140		$query1_meta = $metaquery;
141		$query1_meta[] = $machine_meta;
142		$query1_meta[] = $report_meta;
143		$query1 = new WP_Query (array(
144			'post_type' => $post_type,
145			'tax_query' => $taxquery,
146			'meta_query' => $query1_meta,
147			'posts_per_page' => -1,
148			'meta_key' => 'shop_name',
149			'orderby' => 'meta_value',
150			'order' => 'ASC'
151			));
152
153		// 2.台データURLなし/取材IDあり
154		$query2_meta = $metaquery;
155		$query2_meta[] = $no_machine_meta;
156		$query2_meta[] = $report_meta;
157		$query2 = new WP_Query (array(
158			'post_type' => $post_type,
159			'tax_query' => $taxquery,
160			'meta_query' => $query2_meta,
161			'posts_per_page' => -1,
162			'meta_key' => 'shop_name',
163			'orderby' => 'meta_value',
164			'order' => 'ASC'
165			));
166
167		// 3.台データURLあり/取材IDなし
168		$query3_meta = $metaquery;
169		$query3_meta[] = $machine_meta;
170		$query3_meta[] = $no_report_meta;
171		$query3 = new WP_Query (array(
172			'post_type' => $post_type,
173			'tax_query' => $taxquery,
174			'meta_query' => $query3_meta,
175			'posts_per_page' => -1,
176			'meta_key' => 'shop_name',
177			'orderby' => 'meta_value',
178			'order' => 'ASC'
179			));
180
181		// 4.台データURLなし/取材IDなし
182		$query4_meta = $metaquery;
183		$query4_meta[] = $no_machine_meta;
184		$query4_meta[] = $no_report_meta;
185		$query4 = new WP_Query (array(
186			'post_type' => $post_type,
187			'tax_query' => $taxquery,
188			'meta_query' => $query4_meta,
189			'posts_per_page' => -1,
190			'meta_key' => 'shop_name',
191			'orderby' => 'meta_value',
192			'order' => 'ASC'
193			));

###試したこと

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

行動規範の内容に同意します

回答2件

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

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

PHP
1$query0_meta = $metaquery;
2$query0 = new WP_Query (array(
3    'post_type' => $post_type,
4    'tax_query' => $taxquery,
5    'meta_query' => $query0_meta,
6    'posts_per_page' => -1,
7    'meta_key' => 'shop_name',
8    'orderby' => 'meta_value',
9    'order' => 'ASC'
10    ));

また、このクエリを取得する部分がどこに書かれているのか提示されていませんが、もし固定ページ／検索ページのテンプレート上に書かれている場合は、ページ自体の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 06:16

kei344

総合スコア69398

SSaka

2016/06/25 07:11

こんにちは。いつもお世話になりっぱなしです。ありがとうございます。 query1～query4の問い合わせの重複は自分も非常に気になっていました。主に、この「台データ」、「取材ID」の部分はmetaデータなため、 smart custom fieldの関数でwhileする度に再度カスタムフィールドを変数に再問い合わせするような構造にしてしまっていて、３分以上応答がなくなってしまうことがあり、クエリ側で除外してみよう、と試した結果です。 query0の変数だけで再問い合わせ無くmeta_key+meta_valueを取得分岐することができれば、表示側で負荷を増やさずに分岐できそうです。とくに悩んだところは、表示優先順位で、 shop_nameのあいうえお順ソートを前提に、 query1,2,3の順番に結果を表示、それ以外はsort4という感じにしたいというとこでした。 search.phpにおいているのですが、ページ自体が、１回クエリを発行していてさらに、WP_Queryしているというところは構造的に自分で理解できていなかった点でした。 DBのキャッシュも考えていましたが的確なプラグインに出会えていませんでした。プラグインをためしつつもう少し検討してみます。

kei344

2016/06/25 08:22

> 再度カスタムフィールドを変数に再問い合わせするような構造にしてしまっていてすいません、その点を見落としていました。確かにDBへの問い合わせが発生しますね。カテゴリやタグも確認しましたが、全て取得時にDBへの問い合わせが発生します。結局4つのクエリを投げるのが一番低コストですね…。

行動規範の内容に同意します

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

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

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

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

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

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

MySQL 5.1のスロークエリログ

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

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

投稿2016/06/24 13:25

obi_yuta

総合スコア121

SSaka

2016/06/24 19:13

ありがとうございます。全て重いと考えていたのですが、件数が多いもののフィルタに時間がかかっていました。複合クエリでleft joinいっぱいなせいか、イマイチこれがいい！というインデックスがまだつくれずにいます。。。 Using filesortとかがなくなれば良さそうだってニュアンスは理解できたのですが、では具体的にどうIndexつくるかまだまだ理解できてなさそうです +----+--------------------+-----------------------+--------+--------------------------+----------+---------+-------------------------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-----------------------+--------+--------------------------+----------+---------+-------------------------------------+-------+----------------------------------------------+ | 1 | PRIMARY | wp_postmeta | ref | post_id,meta_key | meta_key | 576 | const | 10166 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | wp_posts | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | hogedb.wp_postmeta.post_id | 1 | Using where | | 1 | PRIMARY | mt1 | ref | post_id | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt2 | ref | post_id,meta_key | post_id | 8 | hogedb.wp_postmeta.post_id | 8 | Using where | | 1 | PRIMARY | mt3 | ref | post_id | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt4 | ref | post_id,meta_key | post_id | 8 | hogedb.wp_postmeta.post_id | 8 | Using where | | 1 | PRIMARY | mt5 | ref | post_id | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt6 | ref | post_id,meta_key | meta_key | 576 | const | 1 | Using where | | 2 | DEPENDENT SUBQUERY | wp_term_relationships | eq_ref | PRIMARY,term_taxonomy_id | PRIMARY | 16 | hogedb.wp_posts.ID,const | 1 | Using index | +----+--------------------+-----------------------+--------+--------------------------+----------+---------+-------------------------------------+-------+----------------------------------------------+

SSaka

2016/06/24 19:13

mysql> show index from wp_postmeta -> ; +-------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | wp_postmeta | 0 | PRIMARY | 1 | meta_id | A | 85385 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | post_id | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | meta_key | 1 | meta_key | A | 78 | 191 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_metakey | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_metakey | 2 | meta_key | A | 85385 | NULL | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_metakey_metavalue | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_metakey_metavalue | 2 | meta_key | A | 85385 | NULL | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_metakey_metavalue | 3 | meta_value | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_metakey | 1 | meta_key | A | 78 | NULL | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_metavalue | 1 | meta_value | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_meta_keyvalue | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_meta_keyvalue | 2 | meta_key | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_meta_keyvalue | 3 | meta_value | A | 85385 | 64 | NULL | YES | BTREE | | +-------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ mysql> explain SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = 'p_data' ) LEFT JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) LEFT JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id AND mt4.meta_key = 's_data' ) LEFT JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id ) LEFT JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id AND mt6.meta_key = 'report_exists' ) WHERE 1=1 AND ( ( SELECT COUNT(1) FROM wp_term_relationships WHERE term_taxonomy_id IN (30) AND object_id = wp_posts.ID ) = 1 ) AND ( wp_postmeta.meta_key = 'shop_name' AND ( ( ( ( mt1.meta_key = 'p_data' AND CAST(mt1.meta_value AS CHAR) = '' ) OR mt2.post_id IS NULL ) AND ( ( mt3.meta_key = 's_data' AND CAST(mt3.meta_value AS CHAR) = '' ) OR mt4.post_id IS NULL ) ) AND ( ( mt5.meta_key = 'report_exists' AND CAST(mt5.meta_value AS CHAR) = '' ) OR ( mt5.meta_key = 'report_exists' AND CAST(mt5.meta_value AS CHAR) = '' ) OR mt6.post_id IS NULL ) ) ) AND wp_posts.post_type = 'shop_data' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC; +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | wp_posts | ref | PRIMARY,type_status_date | type_status_date | 124 | const,const | 9311 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | wp_postmeta | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_posts.ID,const | 1 | Using where | | 1 | PRIMARY | mt1 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt2 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_postmeta.post_id,const | 1 | Using where; Using index | | 1 | PRIMARY | mt3 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt4 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_postmeta.post_id,const | 1 | Using where; Using index | | 1 | PRIMARY | mt5 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt6 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | meta_key | 576 | const | 1 | Using where | | 2 | DEPENDENT SUBQUERY | wp_term_relationships | eq_ref | PRIMARY,term_taxonomy_id | PRIMARY | 16 | hogedb.wp_posts.ID,const | 1 | Using index | +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+----------------------------------------------+

obi_yuta

2016/06/24 23:49

おっしゃるとおり「Using temporary; Using filesort」の部分がどうにかできれば良さそうですね。まず、 explain SELECT wp_posts.* FROM wp_posts... の結果の「type_status_date | .. | 9311 | Using where; Using temporary; Using filesort |」の部分ですが、where句でpost_type=, post_status=を指定しているからかと思うのですが、名前から察するにpost_type, post_status, post_dateというインデックスになっているため、意味がなくなってしまっている気がします。 create index idx_type_status on wp_posts(post_type,post_status); といった感じでwp_postsに対してindexを作成してやるとどうでしょうか？（post_type,post_statusそれぞれにもindexを作成しておいてもいいかもです） 1番上に記載していただいている下記を含むexplainの結果はどちらのクエリのものでしょうか？ | 1 | PRIMARY | wp_postmeta | ref | post_id,meta_key | meta_key | 576 | const | 10166 | Using where; Using temporary; Using filesort |

SSaka

2016/06/25 03:54 編集

ありがとうございます。こういったインデックスの部分は経験則がものを言うものなのでしょうか。。。１個目と２個目のEXPLAINかけたSQLは同じものです（コピペたりずもうしわけありません）頂いたインデックスを貼ってみてみます

SSaka

2016/06/25 03:49 編集

mysql> create index idx_type_status on wp_posts(post_type,post_status); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 19 Current database: hogedb Query OK, 11610 rows affected (0.49 sec) Records: 11610 Duplicates: 0 Warnings: 0 mysql> explain SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = 'p_data' ) LEFT JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) LEFT JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id AND mt4.meta_key = 's_data' ) LEFT JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id ) LEFT JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id AND mt6.meta_key = 'report_exists' ) WHERE 1=1 AND ( ( SELECT COUNT(1) FROM wp_term_relationships WHERE term_taxonomy_id IN (30) AND object_id = wp_posts.ID ) = 1 ) AND ( wp_postmeta.meta_key = 'shop_name' AND ( ( ( ( mt1.meta_key = 'p_data' AND CAST(mt1.meta_value AS CHAR) = '' ) OR mt2.post_id IS NULL ) AND ( ( mt3.meta_key = 's_data' AND CAST(mt3.meta_value AS CHAR) = '' ) OR mt4.post_id IS NULL ) ) AND ( ( mt5.meta_key = 'report_exists' AND CAST(mt5.meta_value AS CHAR) = '' ) OR ( mt5.meta_key = 'report_exists' AND CAST(mt5.meta_value AS CHAR) = '' ) OR mt6.post_id IS NULL ) ) ) AND wp_posts.post_type = 'shop_data' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC; +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | wp_posts | ref | PRIMARY,type_status_date,idx_type_status | type_status_date | 124 | const,const | 9311 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | wp_postmeta | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_posts.ID,const | 1 | Using where | | 1 | PRIMARY | mt1 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_postmeta.post_id | 8 | | | 1 | PRIMARY | mt2 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_posts.ID,const | 1 | Using where; Using index | | 1 | PRIMARY | mt3 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_postmeta.post_id | 8 | | | 1 | PRIMARY | mt4 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_posts.ID,const | 1 | Using where; Using index | | 1 | PRIMARY | mt5 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_postmeta.post_id | 8 | | | 1 | PRIMARY | mt6 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | meta_key | 576 | const | 1 | Using where | | 2 | DEPENDENT SUBQUERY | wp_term_relationships | eq_ref | PRIMARY,term_taxonomy_id | PRIMARY | 16 | hogedb.wp_posts.ID,const | 1 | Using index | +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------+------+----------------------------------------------+ 9 rows in set (0.01 sec)

SSaka

2016/06/25 03:49

mysql> create index idx_type on wp_posts(post_type); Query OK, 11610 rows affected (0.53 sec) Records: 11610 Duplicates: 0 Warnings: 0 mysql> create index idx_status on wp_posts(post_status); Query OK, 11610 rows affected (0.58 sec) Records: 11610 Duplicates: 0 Warnings: 0 mysql> explain SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id AND mt2.meta_key = 'p_data' ) LEFT JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) LEFT JOIN wp_postmeta AS mt4 ON (wp_posts.ID = mt4.post_id AND mt4.meta_key = 's_data' ) LEFT JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id ) LEFT JOIN wp_postmeta AS mt6 ON (wp_posts.ID = mt6.post_id AND mt6.meta_key = 'report_exists' ) WHERE 1=1 AND ( ( SELECT COUNT(1) FROM wp_term_relationships WHERE term_taxonomy_id IN (30) AND object_id = wp_posts.ID ) = 1 ) AND ( wp_postmeta.meta_key = 'shop_name' AND ( ( ( ( mt1.meta_key = 'p_data' AND CAST(mt1.meta_value AS CHAR) = '' ) OR mt2.post_id IS NULL ) AND ( ( mt3.meta_key = 's_data' AND CAST(mt3.meta_value AS CHAR) = '' ) OR mt4.post_id IS NULL ) ) AND ( ( mt5.meta_key = 'report_exists' AND CAST(mt5.meta_value AS CHAR) = '' ) OR ( mt5.meta_key = 'report_exists' AND CAST(mt5.meta_value AS CHAR) = '' ) OR mt6.post_id IS NULL ) ) ) AND wp_posts.post_type = 'shop_data' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC; +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | wp_posts | ref | PRIMARY,type_status_date,idx_type_status,idx_type,idx_status | type_status_date | 124 | const,const | 9311 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | wp_postmeta | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_posts.ID,const | 1 | Using where | | 1 | PRIMARY | mt1 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt2 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_postmeta.post_id,const | 1 | Using where; Using index | | 1 | PRIMARY | mt3 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_posts.ID | 8 | | | 1 | PRIMARY | mt4 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | idx_postid_metakey | 776 | hogedb.wp_postmeta.post_id,const | 1 | Using where; Using index | | 1 | PRIMARY | mt5 | ref | post_id,idx_postid_metakey,idx_postid_metakey_metavalue,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | post_id | 8 | hogedb.wp_postmeta.post_id | 8 | | | 1 | PRIMARY | mt6 | ref | post_id,meta_key,idx_postid_metakey,idx_postid_metakey_metavalue,idx_metakey,idx_postid,idx_postid_meta_keyvalue,idx_postid_meta_valuekey | meta_key | 576 | const | 1 | Using where | | 2 | DEPENDENT SUBQUERY | wp_term_relationships | eq_ref | PRIMARY,term_taxonomy_id | PRIMARY | 16 | hogedb.wp_posts.ID,const | 1 | Using index | +----+--------------------+-----------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------+-------------------------------------------+------+----------------------------------------------+ 9 rows in set (0.03 sec)

SSaka

2016/06/25 03:49

mysql> show index from wp_posts; +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | wp_posts | 0 | PRIMARY | 1 | ID | A | 11610 | NULL | NULL | | BTREE | | | wp_posts | 1 | post_name | 1 | post_name | A | 11610 | 191 | NULL | | BTREE | | | wp_posts | 1 | type_status_date | 1 | post_type | A | 11 | NULL | NULL | | BTREE | | | wp_posts | 1 | type_status_date | 2 | post_status | A | 16 | NULL | NULL | | BTREE | | | wp_posts | 1 | type_status_date | 3 | post_date | A | 1451 | NULL | NULL | | BTREE | | | wp_posts | 1 | type_status_date | 4 | ID | A | 11610 | NULL | NULL | | BTREE | | | wp_posts | 1 | post_parent | 1 | post_parent | A | 76 | NULL | NULL | | BTREE | | | wp_posts | 1 | post_author | 1 | post_author | A | 2 | NULL | NULL | | BTREE | | | wp_posts | 1 | idx_type_status | 1 | post_type | A | 11 | NULL | NULL | | BTREE | | | wp_posts | 1 | idx_type_status | 2 | post_status | A | 16 | NULL | NULL | | BTREE | | | wp_posts | 1 | idx_type | 1 | post_type | A | 11 | NULL | NULL | | BTREE | | | wp_posts | 1 | idx_status | 1 | post_status | A | 5 | NULL | NULL | | BTREE | | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 12 rows in set (0.00 sec) mysql> show index from wp_postsmeta; ERROR 1146 (42S02): Table 'hogedb.wp_postsmeta' doesn't exist mysql> show index from wp_postmeta; +-------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | wp_postmeta | 0 | PRIMARY | 1 | meta_id | A | 85385 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | post_id | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | meta_key | 1 | meta_key | A | 78 | 191 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_metakey | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_metakey | 2 | meta_key | A | 85385 | NULL | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_metakey_metavalue | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_metakey_metavalue | 2 | meta_key | A | 85385 | NULL | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_metakey_metavalue | 3 | meta_value | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_metakey | 1 | meta_key | A | 78 | NULL | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_metavalue | 1 | meta_value | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_meta_keyvalue | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_meta_keyvalue | 2 | meta_key | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_meta_keyvalue | 3 | meta_value | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_meta_valuekey | 1 | post_id | A | 10673 | NULL | NULL | | BTREE | | | wp_postmeta | 1 | idx_postid_meta_valuekey | 2 | meta_value | A | 85385 | 64 | NULL | YES | BTREE | | | wp_postmeta | 1 | idx_postid_meta_valuekey | 3 | meta_key | A | 85385 | 64 | NULL | YES | BTREE | | +-------------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 17 rows in set (0.00 sec)

SSaka

2016/06/25 03:53

EXPLAIN結果の読み方としては、各行のExtraで、Using indexが含まれていれば効果的にインデックスから読まれているということでしょうか。たとえば足りないIndexを探す場合、using indxの無い行を全てusing indexとする方向で試して、その組合せの結果、１個目のusing temporary / using filesortが消えるのではないかな？という推測を考えています

obi_yuta

2016/06/25 06:10

出先のため確認が遅くなりすいません。 >> EXPLAIN結果の読み方としては、各行のExtraで、Using indexが含まれていれば効果的にインデックスから読まれているということでしょうか。そうですね。Using indexはインデックスから問い合わせ結果を返せているということです。 >> たとえば足りないIndexを探す場合、using indxの無い行を全てusing indexとする方向で試して、その組合せの結果、１個目の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の条件を少しずつ削ってみてどうかも試されてみると良いかと思います。

SSaka

2016/06/25 06: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 = 記事番号,記事番号,記事番号というイメージで除外＋５４秒かかったクエリを０．１秒以下にすることができまし。ひとまずは前に進んだのですが、今後のためにもチューニングは引続き試してみたいと思います。頂いた、SQLを元に検索しても、やはりusing filesortは消えませんでした。 slow queryのSQLをみてleft joinの多さに知識ないなりにもびっくりした面もあるので、もうすこしシンプルなSQLで、このあたりを解消できるSQL＋filesortをさせないというところを念頭において試してみます。

行動規範の内容に同意します

あなたの回答