SQLを軽くしたい

下記のSQLを軽くしたいです。

読み込みが遅すぎて、結果が表示されません。

###目的
高速に表示されるようにしたいです。
Advance custom fieldを使用して、その値などを取得しています。

//一覧情報取得
$result = $mydb->get_results("
SELECT post_title, id, post_name, m1.meta_value AS 'name_furigana', m2.meta_value AS 'photo_official', m3.meta_value AS 'position', m4.meta_value AS 'association', m5.meta_value AS 'number', m6.meta_value AS 'place', m7.meta_value AS 'graduate', m8.meta_value AS 'hobby', m9.meta_value AS 'motto', m10.meta_value AS 'greeting', m11.meta_value AS 'photo_working', m12.meta_value AS 'greeting_accident', m13.meta_value AS 'greeting_criminal'
FROM $mydb->posts
INNER JOIN $mydb->postmeta AS m1
	ON m1.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m2
	ON m2.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m3
	ON m3.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m4
	ON m4.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m5
	ON m5.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m6
	ON m6.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m7
	ON m7.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m8
	ON m8.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m9
	ON m9.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m10
	ON m10.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m11
	ON m11.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m12
	ON m12.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
INNER JOIN $mydb->postmeta AS m13
	ON m13.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
WHERE post_type = 'bio'
AND post_status = 'publish'
AND m1.meta_key = 'name_furigana'
AND m2.meta_key = 'photo_official'
AND m3.meta_key = 'position'
AND m4.meta_key = 'association'
AND m5.meta_key = 'number'
AND m6.meta_key = 'place'
AND m7.meta_key = 'graduate'
AND m8.meta_key = 'hobby'
AND m9.meta_key = 'motto'
AND m10.meta_key = 'greeting'
AND m11.meta_key = 'photo_working'
AND m12.meta_key = 'greeting_accident'
AND m13.meta_key = 'greeting_criminal'
AND post_name = 'myname'
");


//表示
foreach ($result as $value) {
	
$the_post_id = $value->id;

$sql_for_eyecatch = "
SELECT meta_value
FROM $mydb->postmeta
WHERE post_id = (SELECT meta_value FROM $mydb->postmeta WHERE post_id = $the_post_id AND meta_key = '_thumbnail_id') AND meta_key = '_wp_attached_file'";

$eyecatch = $mydb->get_var($sql_for_eyecatch);


$the_image = $value->photo_working;

$sql_for_imagework = "
SELECT guid
FROM $mydb->posts
WHERE ID = $the_image";

$imagework = $mydb->get_var($sql_for_imagework);

		
print('<li>'.$value->post_title.'</li></ul></div>
<h2 class="title">'.$value->post_title.'</h2>
<div class="profile-detail">
<div class="clearfix">
<div class="image1"><img width="300px" src="'.$eyecatch.'" alt="'.$value->post_title.'" /></div>
<div class="text">

<h5 class="name"><span class="wide">'.$value->post_title.'</span>（'.$value->name_furigana.'）</h5>
<table>

	<tbody>
	<tr>
		<th style="width: 120px;">テスト１</th>
		<td style="width: 230px;">'.$value->position.'</td>
	</tr>
	<tr>
		<th>テスト２</th>
		<td>'.$value->association.'</td>

	</tr>
	<tr>
		<th>テスト３</th>
		<td>'.$value->number.'</td>

	</tr>
	<tr>
		<th>テスト４</th>
		<td>'.$value->place.'</td>

	</tr>
	<tr>
		<th>テスト５</th>
		<td>'.$value->graduate.'</td>
	</tr>
	<tr>
		<th>テスト６</th>
		<td>'.$value->hobby.'</td>
	</tr>
	<tr>
		<th>テスト７</th>
		<td>'.$value->motto.'</td>
	</tr>
</tbody></table>
<img src="'.$imagework.'" alt="'.$value->post_title.'" />
</div>
</div>
<h4 class="clr">テスト８</h4>
<div class="greeting clearfix">
<p>'.$value->greeting.'</p>
</div>
<h4 class="clr">テスト９</h4>
<div class="greeting clearfix">
<p>'.$value->greeting_accident.'</p>
</div>
<h4 class="clr">テスト１０</h4>
<div class="greeting clearfix">
<p>'.$value->greeting_criminal.'</p>
</div>
</div>');

}

takotakot

2016/02/19 05:58

「具体的に何ができればよいか」をきちんと質問に書けば、よい SQL は得られると思います。

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

回答8件

SQLの実行計画を分析すれば、インデックスが効いているかどうかもわかると思うのでやるべき。

SQL Server: SQL Server クエリのパフォーマンスを最適化する
https://technet.microsoft.com/ja-jp/magazine/2007.11.sqlquery.aspx

投稿2016/02/10 07:39

退会済みユーザー

総合スコア0

ByronHasegawa

2016/02/18 07:48

ありがとうございます。参考にさせて頂きました。

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

joinが多いのがパフォーマンスを下げているのでは？
SQLを実行できる環境がないので、イメージですが、

SQL
1SELECT post_title, id, post_name
2 , case
3       when m1.meta_key = 'name_furigana' then m1.meta_value 
4   end AS 'name_furigana'
5 , case
6       when m1.meta_key = 'photo_official' then m1.meta_value 
7   end AS 'photo_official'
8-- 中略
9FROM $mydb->posts
10INNER JOIN $mydb->postmeta AS m1
11    ON m1.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */
12WHERE post_type = 'bio'
13AND post_status = 'publish'
14AND m1.meta_key in('name_furigana'
15                 , 'photo_official'
16                 , 'position'
17                 , 'association'
18                 , 'number'
19                 , 'place'
20                 , 'graduate'
21                 , 'hobby'
22                 , 'motto'
23                 , 'greeting'
24                 , 'photo_working'
25                 , 'greeting_accident'
26                 , 'greeting_criminal')
27AND post_name = 'myname'

といった感じに変更されては？

投稿2016/02/10 04:38

Orlofsky

総合スコア16415

ByronHasegawa

2016/02/18 07:44

ありがとうございます。実行するとなぜかコンテンツが繰り返し（いくつも）表示されます。

takotakot

2016/02/19 06:00 編集

ByronHasegawa 様速度はどうなりましたか？速度さえ良ければ、あとは繰り返しを消すように PHP で工夫するだけでは？以下追記: もしかして、あとは GROUP BY id するだけかな。

Orlofsky

2016/02/19 06:40

create table文と修正したselect 文を提示できますか？

ByronHasegawa

2016/02/29 01:59 編集

こちらが修正したselect文です。 SELECT post_title, id, post_name , case when m1.meta_key = 'name_furigana' then m1.meta_value end AS 'name_furigana' , case when m1.meta_key = 'position' then m1.meta_value end AS 'position' , case when m1.meta_key = 'association' then m1.meta_value end AS 'association' , case when m1.meta_key = 'number' then m1.meta_value end AS 'number' , case when m1.meta_key = 'place' then m1.meta_value end AS 'place' , case when m1.meta_key = 'graduate' then m1.meta_value end AS 'graduate' , case when m1.meta_key = 'hobby' then m1.meta_value end AS 'hobby' , case when m1.meta_key = 'motto' then m1.meta_value end AS 'motto' , case when m1.meta_key = 'greeting' then m1.meta_value end AS 'greeting' , case when m1.meta_key = 'photo_working' then m1.meta_value end AS 'photo_working' , case when m1.meta_key = 'greeting_accident' then m1.meta_value end AS 'greeting_accident' , case when m1.meta_key = 'greeting_criminal' then m1.meta_value end AS 'greeting_criminal' FROM $mydb->posts INNER JOIN $mydb->postmeta AS m1 ON m1.post_id = $mydb->posts.ID /* テーブルを結合-カスタムフィールド */ WHERE post_type = 'bio' AND post_status = 'publish' AND m1.meta_key in('name_furigana' , 'position' , 'association' , 'number' , 'place' , 'graduate' , 'hobby' , 'motto' , 'greeting' , 'photo_working' , 'greeting_accident' , 'greeting_criminal') AND post_name = 'name' GROUP BY m1.post_id HAVING COUNT(post_id) = 12;

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

postsテーブルに検索用のインデックスは作られていますか？
もし作られていないようでしたらWHERE条件で指定している項目でインデックスを作ってみてはいかがでしょうか？

SQL
1ALTER TABLE posts ADD INDEX posts_idx1(post_type, post_status, post_name);

インデックス作成前後の速度の違いと作成後の実行計画も見てみたいです。
(select文の前にexplainをつけて実行。 explain select ～)

投稿2016/03/01 04:33

nabe3

総合スコア345

ByronHasegawa

2016/03/03 07:52

ありがとうございます。参考にさせて頂き、使用しました。

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

postmetaは巨大なキーバリューテーブルになっているように見えます。
ですので、postmetaをkey単位で分割した後にJOINするようにしてはどうでしょうか？

SQL
1SELECT m0.post_title, m0.id, m0.post_name, m1.meta_value AS 'name_furigana', m2.meta_value AS 'photo_official', m3.meta_value AS 'position', m4.meta_value AS 'association', m5.meta_value AS 'number', m6.meta_value AS 'place', m7.meta_value AS 'graduate', m8.meta_value AS 'hobby', m9.meta_value AS 'motto', m10.meta_value AS 'greeting', m11.meta_value AS 'photo_working', m12.meta_value AS 'greeting_accident', m13.meta_value AS 'greeting_criminal'
2FROM (SELECT post_title, id, post_name FROM $mydb->posts WHERE post_type = 'bio' AND post_status = 'publish') AS m0
3INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'name_furigana') AS m1
4    ON m1.post_id = m0.id /* テーブルを結合-カスタムフィールド */
5INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'photo_official') AS m2
6    ON m2.post_id = m0.id /* テーブルを結合-カスタムフィールド */
7INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'position') AS m3
8    ON m3.post_id = m0.id /* テーブルを結合-カスタムフィールド */
9INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'association') AS m4
10    ON m4.post_id = m0.id /* テーブルを結合-カスタムフィールド */
11INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'number') AS m5
12    ON m5.post_id = m0.id /* テーブルを結合-カスタムフィールド */
13INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'place') AS m6
14    ON m6.post_id = m0.id /* テーブルを結合-カスタムフィールド */
15INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'graduate') AS m7
16    ON m7.post_id = m0.id /* テーブルを結合-カスタムフィールド */
17INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'hobby') AS m8
18    ON m8.post_id = m0.id /* テーブルを結合-カスタムフィールド */
19INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'motto') AS m9
20    ON m9.post_id = m0.id /* テーブルを結合-カスタムフィールド */
21INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'greeting') AS m10
22    ON m10.post_id = m0.id /* テーブルを結合-カスタムフィールド */
23INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'photo_working') AS m11
24    ON m11.post_id = m0.id /* テーブルを結合-カスタムフィールド */
25INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'greeting_accident') AS m12
26    ON m12.post_id = m0.id /* テーブルを結合-カスタムフィールド */
27INNER JOIN (SELECT post_id, meta_value FROM $mydb->postmeta WHERE meta_key = 'greeting_criminal') AS m13
28    ON m13.post_id = m0.id /* テーブルを結合-カスタムフィールド */
29WHERE post_name = 'myname'

最後に本当の検索キー？のpost_nameで絞り込んでいます。

投稿2016/02/10 10:12

jinskyline

総合スコア42

ByronHasegawa

2016/02/18 07:50

ありがとうございます。取得したい値（INNER JOIN）が増えると読み込みも重くなるのですか？？現在はこの提示して頂いたソースでどうにか表示はできるようになりましたが、やはり表示に３分くらいはかかります。

takotakot

2016/02/19 06:12

INNER JOIN が増えると、結合した表を作ろうとするので、重くなりますよ。

ByronHasegawa

2016/02/22 00:54

そうなんですね。無知ですみません。。。ありがとうございます。

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

確かにサーバーがかわいそうなSQLではありますが、
やりたいことを推察するにそうSQLを直に書かずに
WordpressなのでWordpressの関数に任せられるものは任せた方が良いと思います。

php
1<?php
2$query = new WP_Query(array(
3		'post_status' => "publish",
4		'post_type' => "bio",
5		'showposts' => -1,
6));
7$posts = $query->posts;
8foreach ($posts as $post):
9$meta = get_metadata('post', $post->ID);
10?>
11<li><?php echo $post->post_title;?></li></ul></div>
12<h2 class="title"><?php echo $post->post_title;?></h2>
13<div class="profile-detail">
14<div class="clearfix">
15<div class="image1"><img width="300px" src="<?php echo get_the_post_thumbnail_url($post);?>" alt="<?php echo $post->post_title;?>" /></div>
16<div class="text">
17<h5 class="name"><span class="wide"><?php echo $post->post_title;?></span>（<?php echo $meta['name_furigana'][0];?>）</h5>
18<table>
19    <tbody>
20    <tr>
21        <th style="width: 120px;">テスト１</th>
22        <td style="width: 230px;"><?php echo $meta['position'][0];?></td>
23    </tr>
24<!-- 略 -->
25    </tbody>
26</table>
27<img src="<?php echo get_the_post_thumbnail_url($post, 'full');?>" alt="<?php echo $post->post_title;?>" />
28</div>
29</div>
30<h4 class="clr">テスト８</h4>
31<div class="greeting clearfix">
32<p><?php echo $meta['greeting'][0];?></p>
33</div>
34<!-- 略 -->
35</div>
36<?php endforeach;?>

ちなみに get_the_post_thumbnail_url() はWordpressのversion4.4以降でないと使えません。
ですので未満の場合はget_the_post_thumbnail()で。

投稿2016/02/10 04:49

ki6ool

総合スコア150

ByronHasegawa

2016/02/18 08:00 編集

ありがとうございます。別のデーターベースから読み込んでいるため、SQLを使うしかないです。

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

こんにちは。
SQL Serverはあまり触ったことがないので、違っていたらご容赦ください。

WHERE句に指定している条件を、各INNER JOINの条件に追加してはどうでしょう？
扱うデータの量を最初に少なくしてあげれば、少しは軽くなるかと思います。

また、テーブル名のついていないカラム
post_type、post_status、post_nameはどのテーブルに存在するんでしょうか？
ここも、テーブル名を指定してあげたほうが言いと思います。

投稿2016/02/10 04:26

kaputaros

総合スコア1844

ByronHasegawa

2016/02/18 07:42

ありがとうございます。参考にさせて頂きました。

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

ベストアンサー

JOINを排除したSQLをつくってみました。
「JOINした場合、100件のテーブルと２００件のテーブルをJOINした場合
１００×２００＝2万件のデータが、メモリにロードされてから、条件に合ったものを
探しているとイメージすると、JOINがどれだけ遅くなる原因になるか想像できる思います。」
$result = $mydb->get_results("
SELECT
m0.post_title,
m0.id,
m0.post_name,
(select m1.meta_value from $mydb->postmeta m1 where m1.post_id = m0.id and m1.meta_key = 'name_furigana' ) AS 'name_furigana',
(select m2.meta_value from $mydb->postmeta m2 where m2.post_id = m0.id and m2.meta_key = 'photo_official' ) AS 'photo_official',
(select m3.meta_value from $mydb->postmeta m3 where m3.post_id = m0.id and m3.meta_key = 'position' ) AS 'position',
(select m4.meta_value from $mydb->postmeta m4 where m4.post_id = m0.id and m4.meta_key = 'association' ) AS 'association',
(select m5.meta_value from $mydb->postmeta m5 where m5.post_id = m0.id and m5.meta_key = 'number' ) AS 'number',
(select m6.meta_value from $mydb->postmeta m6 where m6.post_id = m0.id and m6.meta_key = 'place' ) AS 'place',
(select m7.meta_value from $mydb->postmeta m7 where m7.post_id = m0.id and m7.meta_key = 'graduate' ) AS 'graduate',
(select m8.meta_value from $mydb->postmeta m8 where m8.post_id = m0.id and m8.meta_key = 'hobby' ) AS 'hobby',
(select m9.meta_value from $mydb->postmeta m9 where m9.post_id = m0.id and m9.meta_key = 'motto' ) AS 'motto',
(select m10.meta_value from $mydb->postmeta m10 where m10.post_id = m0.id and m10.meta_key = 'greeting' ) AS 'greeting',
(select m11.meta_value from $mydb->postmeta m11 where m11.post_id = m0.id and m11.meta_key = 'photo_working' ) AS 'photo_working',
(select m12.meta_value from $mydb->postmeta m12 where m12.post_id = m0.id and m12.meta_key = 'greeting_accident' ) AS 'greeting_accident',
(select m13.meta_value from $mydb->postmeta m13 where m13.post_id = m0.id and m13.meta_key = 'greeting_criminal' ) AS 'greeting_criminal'
FROM ( select post_title, id, post_name from $mydb->posts where post_type = 'bio' and AND post_status = 'publish' AND AND post_name = 'myname' ) AS m0
");

投稿2016/03/03 07:22

退会済みユーザー

総合スコア0

ByronHasegawa

2016/03/03 07:53

とても分かりやすい回答ありがとうございます。とても、早く読み込まれました！ありがとうございます！

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

疑問点: $mydb->postmeta には、どのような index が張られているか。
「実行計画を分析」の結果はどうなったか。

私もイメージだけで、申し訳ないです。方向性としては、Orlofsky 様の方向が一番良さそうです。
一発でやろうとしてもいいですが、意図を考えると

SQL
1
2SELECT post_id FROM $mydb->postmeta
3WHERE meta_key
4                 in('name_furigana'
5                 , 'photo_official'
6                 , 'position'
7                 , 'association'
8                 , 'number'
9                 , 'place'
10                 , 'graduate'
11                 , 'hobby'
12                 , 'motto'
13                 , 'greeting'
14                 , 'photo_working'
15                 , 'greeting_accident'
16                 , 'greeting_criminal')
17GROUP BY post_id
18HAVING COUNT(post_id) = 13;

で、postmeta について条件を満たす id 一覧を作ってから考えた方が楽だと思います。
この id を var_dump して、条件を満たしていそうであれば(正しい id 集合で、

SQL
1WHERE post_type = 'bio'
2AND post_status = 'publish'

以外を満たしていれば)、これをサブクエリに使って絞り込む方がずっと高速だと思います。

投稿2016/02/19 06:46

編集2016/02/19 06:58

takotakot

総合スコア1111

ByronHasegawa

2016/02/22 01:16

ありがとうございます。やってみましたが、結果が表示されませんでした。

ByronHasegawa

2016/02/22 01:16

SELECT post_id FROM $mydb->postmeta GROUP BY post_id HAVING COUNT(post_id) = 13; WHERE post_type = 'bio' AND meta_key in('name_furigana' , 'photo_official' , 'position' , 'association' , 'number' , 'place' , 'graduate' , 'hobby' , 'motto' , 'greeting' , 'photo_working' , 'greeting_accident' , 'greeting_criminal') AND post_status = 'publish' AND post_name = 'myname'

takotakot

2016/02/22 01:37

post_type, post_status, post_name は $mydb->postmeta というテーブルにはないのでは？

ByronHasegawa

2016/02/29 03:53

すみません、おそらくそうです。どうやれば良いのかわかりませんので、教えていただいてもよろしいですか？？

takotakot

2016/02/29 11:12

私が提示したそのままの SQL で返ってくるデータが何か分からないと、お伝えができません。

ByronHasegawa

2016/03/01 02:22

申し訳ありません。まだ公開しているサイトではないので、サイトのリンクをお伝えできません。