MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimize subquery, replace IN() with JOIN
Posted by: Miron jajtic
Date: November 15, 2016 04:25PM

Thank you, Peter.
Unfortunately does not work as expected
It returns several rows with the same content.

Here are table structure and sample datas: http://textuploader.com/d5zhr

Test run with your query:

mysql> SELECT a.id, a.title, a.alias
FROM content AS a
JOIN category_content_se AS b ON a.id = b.content_id
WHERE a.id =1842
AND b.content_typ = 'actual'
ORDER BY a.date_add DESC
LIMIT 10;
+------+------------------------+------------------------+
| id | title | alias |
+------+------------------------+------------------------+
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
+------+------------------------+------------------------+
7 rows in set (0.00 sec)


Test run with original query:

mysql> SELECT id, title, alias
FROM content
WHERE published = '1'
AND typ = 'actual'
AND id
IN (
SELECT content_id
FROM category_content_se
WHERE category_id
IN (
SELECT category_id
FROM category_content_se
WHERE content_id = '1842'
)
AND content_typ = 'actual'
)
ORDER BY date_add DESC
LIMIT 10;
+------+--------------------------------------------------+--------------------------------------------------+
| id | title | alias |
+------+--------------------------------------------------+--------------------------------------------------+
| 1876 | Prljava kampanja | Prljava kampanja |
| 1848 | Stablo nerada u Vrhovinama | Stablo nerada u Vrhovinama |
| 1847 | Zbogom Åzivotu pod maskama | Zbogom zivotu pod maskama |
| 1842 | Gdje je nestala pravda | Gdje je nestala pravda |
| 1838 | Pastrva je stara, uprava je nova | Pastrva je stara uprava je nova |
| 1837 | Pozeljna udavaca | Pozeljna udavaca |
| 1835 | Posmrtna knjiga kontraverznog srpskog politicara | Posmrtna knjiga kontraverznog srpskog politicara |
| 1831 | don Angelo iz duse | don Angelo iz duse |
| 1828 | Sajam poslova u Otoccu | Sajam poslova u Otoccu |
| 1824 | Pravda spora, ali dostizna | Pravda spora ali dostizna |
+------+--------------------------------------------------+--------------------------------------------------+
10 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize subquery, replace IN() with JOIN
1097
November 15, 2016 04:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.