Geek asks and answers

Optimize MySQL query with dependent subquery

I need to find a way to eliminate the dependent subquery.

I have an article table that can have several languages. The simplified table structure is as follows:

id, title, language, translation_set_id

1 A    en 0
2 B    en 2
3 B_ru ru 2
4 C    en 4
5 C_ru ru 4
6 D    en 6
7 D_fr fr 6

The value of translation_set_id is 0 when the article has no translations or is specified by the identifier of the base translation. So, B is the original English article, and B_ru is the Russian translation of the article.

I need a request that will allow me to return all Russian articles, or if they do not exist original articles in the language. Therefore he will return.

1 A    en 0
3 B_ru ru 2
5 C_ru ru 4
6 D    en 6

So far I have this:

SELECT id, title, language, translation_set_id
FROM articles a
WHERE 
  a.translation_set_id = 0
  OR (a.language = 'ru')
  OR (a.id = a.translation_set_id AND
       0 = (SELECT COUNT(ac.id)
            FROM articles ac
            WHERE ac.translation_set_id = a.translation_set_id 
            AND ac.language = 'ru')
     )

But this does a subquery for each row, creating a dependent query. Is there a way to resolve the dependent query?

UPDATE: Neels solution seems to work, thanks!

, ? , , , , ( , )?

UPDATE2: , , Neel DRapp. http://www.sqlfiddle.com/#!2/28ca8/18, , .

:

CREATE TABLE articles (
  id INT,
  title VARCHAR(20),
  language VARCHAR(20),
  translation_set_id INT);

INSERT INTO articles values
  (1,'A','en',0),
  (2,'B','en',2),
  (3,'B_ru','ru',2),
  (4,'C','en',4),
  (5,'C_ru','ru',4),
  (6,'D','en',6),
  (7,'D_fr','fr',6),
  (8,'E_ru','ru', 0),
  (9,'F_fr','fr', 0),
  (10,'G_ru','ru', 10),
  (11,'G_fr','fr', 10),
  (12,'G_en','en', 10);

:

SELECT id, title, language, translation_set_id
FROM articles a
WHERE
  a.translation_set_id = 0
  OR (a.language = 'fr')
  OR (a.language = 'ru' AND
       0 = (SELECT COUNT(ac.id)
            FROM articles ac
            WHERE ac.translation_set_id = a.translation_set_id
            AND ac.language = 'fr'))
  OR (a.id = a.translation_set_id AND
       0 = (SELECT COUNT(ac.id)
            FROM articles ac
            WHERE ac.translation_set_id = a.translation_set_id
            AND (ac.language = 'fr' OR ac.language = 'ru'))
     );

:

SELECT  a.*
FROM articles a
LEFT JOIN articles ac ON ac.translation_set_id = a.id
  AND ac.language = 'fr'
LEFT JOIN articles ac2 ON ac2.translation_set_id = a.id
  AND ac2.language = 'ru'
WHERE a.translation_set_id = 0
  OR a.language = 'fr'
  OR (a.language = 'ru' AND ac.id IS NULL)
  OR (a.id = a.translation_set_id AND ac2.id IS NULL AND ac.id IS NULL);
+3
4

SQL:

http://www.sqlfiddle.com/#!2/c05d0/15

.

SELECT  a.*
FROM articles a
LEFT OUTER JOIN articles ac ON ac.translation_set_id = a.translation_set_id 
AND ac.language = 'ru'
WHERE a.translation_set_id = 0
OR a.language = 'ru'
OR (a.id = a.translation_set_id AND ac.id IS NULL);
+1

Ypercube where, coalesce(), .

, = 0, - , , , , - . , .

. "" ( ), , . , , .

SELECT
      a1.id as OriginalAricleID,
      a1.title as OriginalTitle,
      a1.language as OriginalLanguage,
      a2.id as TranslatedAricleID,
      a2.title as TranslatedTitle
   from
      Articles a1
         LEFT JOIN Articles a2
            ON a1.id = a2.translation_set_id
            AND a2.language = 'ru'
   where
         a1.translation_set_id = 0
      OR a1.id = a1.translation_set_id

. , , .

+2

LEFT JOIN:

SELECT a.id, a.title, a.language, a.translation_set_id
  FROM articles a
 LEFT JOIN articles ac ON ac.translation_set_id = a.translation_set_id 
                      AND ac.language = 'ru'
 WHERE a.translation_set_id = 0
    OR (a.language = 'ru')
    OR (    a.id = a.translation_set_id 
        AND ac.id IS NULL
       )
 GROUP BY a.id, a.title, a.language, a.translation_set_id
+1
source

Rewrite this part:

AND
       0 = (SELECT COUNT(ac.id)
            FROM articles ac
            WHERE ac.translation_set_id = a.translation_set_id 
            AND ac.language = 'ru')

in the join condition:

AND NOT EXISTS (
                SELECT 1
                FROM articles ac
                WHERE ac.translation_set_id = a.translation_set_id 
                AND ac.language = 'ru'
)

This can speed up the query, because MySql must always read all the rows to get count (), but when using NOT EXISTS(or EXISTS) it stops reading the table when it finds the 1st row that matches the criteria.

+1
source

More articles:


All Articles