CODEWITHSUNDEEP
mysqlsqlgrails
firnnauriel
firnnauriel

Reputation: 2063

How to improve SQL query w/ JOINs?

I'm using MySQL 5.5. I have these 3 tables: News, NewsSubTopic, SubTopic. I wonder why this query is slow:

SELECT
  DISTINCT COUNT(news0_.id) as col_0_0_ 
FROM
  news news0_
INNER JOIN
  news_sub_topic subtopics1_ 
ON
  news0_.id = subtopics1_.news_sub_topics_id
INNER JOIN
  sub_topic subtopic2_ 
ON
  subtopics1_.sub_topic_id = subtopic2_.id 
WHERE
  (subtopic2_.id IN (55 , 134 , 135 , 52 , 53 , 32 , 54))
  AND news0_.type = 'api';

I already placed index key in news.type. Is it possible to add keys in NewsSubTopic?

Here are some stats: News table has 1,088,126 entries, NewsSubTopic has 823,247 entries, and SubTopic has 168.

Note that that query is generated by a Grails (or Hibernate). Can you please explain on how to debug the query and understand why it is slow (3-5 seconds)? And is there any other way to improve it (I can provide other info that you need).

Upvotes: 2

Views: 159

Answers (3)

Tim
Tim

Reputation: 5421

I don't understand why you are doing this:

        ...
        ON
        news0_.id = subtopics1_.news_sub_topics_id

Wouldn't NEWS.id be its PK and wouldn't there be another column like NEWS.topicid that you'd want to use when joining to topics?

You could try to cull the subtopics first:

            select distinct newssubtopicid from newssubtopic as NS
            inner join subtopic ST on NS.subtopicid = ST.id
            and ST.id in (55 , 134 , 135 , 52 , 53 , 32 , 54)

ST.id is its PK, no? There should be an index on NS.subtopicid. How long does the above query take?

At that point you know which news subtopics you need for pulling the relevant news items:

          select count(NEWS.id) from NEWS
          inner join 
          (
            select distinct newssubtopicid from newssubtopic as NS
            inner join subtopic ST on NS.subtopicid = ST.id
            and ST.id in (55 , 134 , 135 , 52 , 53 , 32 , 54)
          ) as T
          on NEWS.topicid = T.newssubtopicid

Upvotes: 0

Julio Guerra
Julio Guerra

Reputation: 5661

Despite what has already been said, your query might be not Sargable. You will need to create indexes for your join conditions to make you query as less sensitive to the news_sub_topics_id as possible.

Make sure to use very selective indexes too (order your index fields by selectivity (VerySelective, LessSelective, ...).

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425683

You don't need DISTINCT, arguments to COUNT or a reference to sub_topic:

SELECT  COUNT(*)
FROM    news_sub_topic ns
JOIN    news n
ON      n.id = ns.news_sub_topics_id
WHERE   ns.sub_topic_id IN (55 , 134 , 135 , 52 , 53 , 32 , 54)
        AND n.type = 'api'

Create a composite index on news_sub_topic (sub_topic_id, news_sub_topics_id)

Note that DISTINCT COUNT(…) and COUNT(DISTINCT …) are the different things. If you need the latter, use this:

SELECT  COUNT(DISTINCT n.id)
FROM    news_sub_topic ns
JOIN    news n
ON      n.id = ns.news_sub_topics_id
WHERE   ns.sub_topic_id IN (55 , 134 , 135 , 52 , 53 , 32 , 54)
        AND n.type = 'api'

Could you please explain what do you want to do with your query?

Upvotes: 4

Related Questions