[Solved] How do you get other tag to increase intersection constraint?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

How do you get other tag to increase intersection constraint?

I have problem with query, who running in 2 seconds. Because subquery return 600 line, I need query without IN operator or faster query.

Currently, I have this query :

SELECT tag.idtag, tag, COUNT(*) AS nombre
FROM tag, questtag
WHERE tag.idtag = questtag.idtag
  AND idquestion IN (
      SELECT question.idquestion
      FROM question, questtag
      WHERE question.idquestion = questtag.idquestion AND idtag IN (1)
      GROUP BY question.idquestion
      HAVING COUNT(*) = 1
  )
GROUP BY tag
ORDER BY nombre DESC, tag
LIMIT 0, 24

I want say to sql : "Get other tag related with Javascript tag (intersection constraint) like stackoverflow."

I have 3 tables : question, questag and tag. The question and tag are related with questtag table.

Sql schema :

QUESTION (idquestion, question) :

1, "Javascript and Jquery"

2, "HTML, CSS, Javascript"

....

QUESTTAG (#idquestion, #idtag) :

1, 1

1, 2

2, 3

2, 4

2, 1

....

QUESTTAG (#idtag, tag) :

1, Javascript

2, Jquery

3, HTML

4, CSS

....

With Javascript, I have to get Jquery, HTML, CSS. With Javascript, HTML, I have to get CSS.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OFFSET In LIMIT Clause (query line: 32): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
  2. Avoid Subqueries (query line: 11): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  3. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
  4. Mixed Order By Directions Prevents Index Use (query line: 31): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
The optimized query:
SELECT
        tag.idtag,
        tag,
        COUNT(*) AS nombre 
    FROM
        tag,
        questtag 
    WHERE
        tag.idtag = questtag.idtag 
        AND idquestion IN (
            SELECT
                question.idquestion 
            FROM
                question,
                questtag 
            WHERE
                question.idquestion = questtag.idquestion 
                AND idtag IN (
                    1
                ) 
            GROUP BY
                question.idquestion 
            HAVING
                COUNT(*) = 1 
            ORDER BY
                NULL
        ) 
    GROUP BY
        tag 
    ORDER BY
        nombre DESC,
        tag LIMIT 0,
        24

Related Articles



* original question posted on StackOverflow here.