効率的なSQL文をご教授ください。

質問

MySQLの初級者です。
下記のようなテーブル構造があります。

article_flag
-------------　-------------
　aid　　　　display
-------------　-------------
　1　　　　　1
-------------　-------------
　2　　　　　1
-------------　-------------
　3　　　　　1
-------------　-------------
　4　　　　　2
-------------　-------------
...Primary(aid)、INDEX(display)

category
-------------　-------------
　cid　　　　name
-------------　-------------
　1　　　　　カテゴリ1
-------------　-------------
　2　　　　　カテゴリ2
-------------　-------------
　3　　　　　カテゴリ3
-------------　-------------
　4　　　　　カテゴリ4
-------------　-------------
...Primary(cid)、INDEX(name)

article_category
-------------　-------------
　aid　　　　cid
-------------　-------------
　1　　　　　1
-------------　-------------
　1　　　　　4
-------------　-------------
　2　　　　　1
-------------　-------------
　2　　　　　2
-------------　-------------
　2　　　　　4
-------------　-------------
　4　　　　　1
-------------　-------------
　4　　　　　2
-------------　-------------
...INDEX（cid aid)

このような構造の中で、
下記のような出力を行いたいのです。

カテゴリ1　【関連記事 2件】
カテゴリ2　【関連記事 1件】
カテゴリ4　【関連記事 2件】

非表示中の記事（display = 2）は関連記事に上がらず、
関連記事の存在しないカテゴリは出力しないという仕様です。

この場合、どのようなSQL文で対処すればよろしいでしょうか？

現状では、

SELECT c.cid, c.name FROM category as c
INNER JOIN article_category as ac ON c.cid = ac.cid
INNER JOIN article_flag as af ON ac.aid = af.aid and af.display = '1'
GROUP BY c.cid ORDER BY c.name ASC limit 10;

というSQL文でまずは抽出してから、
抽出後のPHPのループ内で、

SELECT count(ac.cid) FROM article_category as ac
INNER JOIN article_flag as af ON ac.aid = af.aid
WHERE af.display = '1' and ac.cid = '{取得したcid}';

とカウントして関連記事数を出すという
回りくどいやり方しか思いついていません。

また例では件数が少ないですが、
実際には、article_flagが500件、categoryが200件あり、
一つの記事に平均で10個近いカテゴリが割り当てられているため、
article_categoryの件数は5000件近くあります。

そのため、最初のSQLクエリがとても遅く、
早くても10秒くらいかかります。

基本的にはlimit 10で10件限定の表示のため、
2番目のクエリは特に重さを感じません。

1つ目のクエリで一度に関連記事数まで取得できれば最善ですが、
難しい場合には、1つ目のクエリでは、
関連記事が存在するかどうかの判定だけでも良いので、
何とか良い方法をご教授の程お願いいたします。

回答No.3

nda23 · ベストアンサー

以下のSQLでいけると思います。

select
max(C.name) as category_name,
count(C.cid) as count_cid
from (article_category A
inner join (select aid from article_flag
where display<>2) B on A.aid=B.aid)
inner join category C on A.cid=C.cid
group by A.cid

お礼

ご回答ありがとうございます。
こちらのSQL文を元に、少し応用した結果、
最初10秒近くかかっていたものが、0.07秒まで短縮できました。
今後もさらに精進して鍛えていきたいと思います。
このたびはありがとうございました！

回答No.2

nora1962

素直に
select category.name, count(*)
from article_category join article_flag
on article_category.aid=article_flag.aid
and article_flag.display=1
join category on article_category.cid=category.cid
group by category.name;
ではダメですか。

お礼

ご回答ありがとうございます。
例はあくまでも要点だけをまとめた簡易なもので、
実際のSQL文には、そのほかにもJOINが10個以上あるような内容です。
ご回答いただいた型で進めますと、
早い時で7秒、遅いと10秒程度かかります。

回答No.1

nda23

MySQLのバージョンを提示してください。
4と5ではサブクエリが使えるかどうかの
違いがあり、これだけで全く異なるSQLに
なります。

お礼

ご回答ありがとうございます。
MySQLのバージョンは5.1.59になります。
よろしくお願いいたします。

私の考えうる狭い範囲でのサブクエリも試しましたが、
どれもむしろ逆に重くなってしまい困っています。

サーバがホスティングの共有MySQLなので、
そこまでパフォーマンスは高くありません。