MySQL Forums
Forum List  »  Optimizer & Parser

Advanced Search
New Topic

Re: Optimize subquery, replace IN() with JOIN
Posted by: Miron jajtic
Date: November 16, 2016 04:03AM

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

Bellow are table structure and sample datas.

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)



Tables with example data:
#################################################

CREATE TABLE IF NOT EXISTS `content` (
`id` int(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`date_add` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`published` tinyint(1) unsigned NOT NULL DEFAULT '0',
`typ` varchar(50) NOT NULL DEFAULT '',
`alias` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `title` (`title`),
KEY `published` (`published`),
KEY `typ` (`typ`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `content` VALUES(1807, 'Zemljopisni polozaj like', '2009-02-23 00:33:17', 1, 'actual', 'Zemljopisni polozaj like');
INSERT INTO `content` VALUES(1808, 'Zanimljive informacije o Lici', '2009-02-23 06:42:57', 1, 'actual', 'Zanimljive informacije o Lici');
INSERT INTO `content` VALUES(1809, 'Licko gospodarstvo danas', '2009-02-23 06:44:48', 1, 'actual', 'Licko gospodarstvo danas');
INSERT INTO `content` VALUES(1810, 'Povijest Like', '2009-02-23 06:46:50', 1, 'actual', 'Povijest Like');
INSERT INTO `content` VALUES(1811, 'Lokalni izbori', '2009-02-23 16:03:34', 1, 'actual', 'Lokalni izbori');
INSERT INTO `content` VALUES(1812, 'Bravo za zdravstvo u Gospicu', '2009-02-23 17:09:48', 1, 'actual', 'Bravo za zdravstvo u Gospicu');
INSERT INTO `content` VALUES(1813, 'Promocija Gacke - sportskim ribolovom u svijet', '2009-02-23 17:35:23', 1, 'actual', 'Promocija Gacke sportskim ribolovom u svijet');
INSERT INTO `content` VALUES(1814, 'Lika ko Amerika - Zasto Slobodna Lika?', '2009-02-23 17:47:36', 1, 'actual', 'Lika ko Amerika Zasto Slobodna Lika');
INSERT INTO `content` VALUES(1817, 'Izazi iz minusa - Povoljno do kredita', '2009-02-23 18:26:11', 0, 'actual', 'Izadi iz minusa Povoljno do kredita');
INSERT INTO `content` VALUES(1818, 'Polomljeni Lovinac - Pustos nakon ledenog nevremena', '2009-02-23 18:36:57', 1, 'actual', 'Polomljeni Lovinac Pustos nakon ledenog nevremena');
INSERT INTO `content` VALUES(1824, 'Pravda spora, ali dostizna', '2009-02-23 19:52:33', 1, 'actual', 'Pravda spora ali dostizna');
INSERT INTO `content` VALUES(1827, 'Beby Udbina!', '2009-02-24 07:03:31', 1, 'actual', 'Beby Udbina');
INSERT INTO `content` VALUES(1828, 'Sajam poslova u Otoccu', '2009-02-24 07:08:23', 1, 'actual', 'Sajam poslova u Otoccu');
INSERT INTO `content` VALUES(1831, 'don Angelo iz duse', '2009-02-24 11:25:34', 1, 'actual', 'don Angelo iz duse');
INSERT INTO `content` VALUES(1835, 'Posmrtna knjiga kontraverznog srpskog politicara', '2009-02-24 13:22:06', 1, 'actual', 'Posmrtna knjiga kontraverznog srpskog politicara');
INSERT INTO `content` VALUES(1837, 'Pozeljna udavaca', '2009-02-24 16:23:25', 1, 'actual', 'Pozeljna udavaca');
INSERT INTO `content` VALUES(1838, 'Pastrva je stara, uprava je nova', '2009-02-24 16:33:21', 1, 'actual', 'Pastrva je stara uprava je nova');
INSERT INTO `content` VALUES(1842, 'Gdje je nestala pravda', '2009-02-24 17:26:11', 1, 'actual', 'Gdje je nestala pravda');
INSERT INTO `content` VALUES(1847, 'Zbogom zivotu pod maskama', '2009-02-24 19:51:32', 1, 'actual', 'Zbogom zivotu pod maskama');
INSERT INTO `content` VALUES(1848, 'Stablo nerada u Vrhovinama', '2009-02-24 20:56:12', 1, 'actual', 'Stablo nerada u Vrhovinama');
INSERT INTO `content` VALUES(1850, 'Uspjesni projekt javno privatnog partnerstva u bolnici Gospić', '2009-02-24 21:31:24', 1, 'actual', 'Uspjesni projekt javno privatnog partnerstva u bolnici Gospic');
INSERT INTO `content` VALUES(1851, 'Poceli dani posta i molitve', '2009-02-25 15:36:30', 1, 'actual', 'Poceli dani posta i molitve');
INSERT INTO `content` VALUES(1852, 'Domace je domace', '2009-02-25 16:20:00', 1, 'actual', 'Domace je domace');
INSERT INTO `content` VALUES(1853, 'Pocelo je pocelo', '2009-02-25 17:06:37', 1, 'actual', 'Pocelo je pocelo');
INSERT INTO `content` VALUES(1854, 'Posipanje pepelom', '2009-02-25 19:38:28', 1, 'actual', 'Posipanje pepelom');
INSERT INTO `content` VALUES(1856, 'Tko to prijeti našem ministru', '2009-02-26 09:32:04', 1, 'actual', 'Tko to prijeti nasem ministru');
INSERT INTO `content` VALUES(1861, 'Made in country', '2009-02-26 16:35:53', 1, 'actual', 'Made in county');
INSERT INTO `content` VALUES(1862, 'Citaj, samo citaj!', '2009-02-26 18:30:33', 1, 'actual', 'Citaj samo citaj');
INSERT INTO `content` VALUES(1872, 'Zabrinjavajuća nezaposlenost', '2009-02-27 14:13:14', 0, 'actual', 'Zabrinjavajuca nezaposlenost');
INSERT INTO `content` VALUES(1876, 'Prljava kampanja', '2009-02-27 15:26:12', 1, 'actual', 'Prljava kampanja');


CREATE TABLE IF NOT EXISTS `category_content_se` (
`category_id` int(10) unsigned NOT NULL DEFAULT '0',
`content_id` int(10) unsigned NOT NULL DEFAULT '0',
`content_typ` varchar(50) NOT NULL DEFAULT '',
KEY `category_id` (`category_id`),
KEY `content_id` (`content_id`),
KEY `content_typ` (`content_typ`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `category_content_se` VALUES(20, 1807, 'actual');
INSERT INTO `category_content_se` VALUES(17, 1807, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1809, 'actual');
INSERT INTO `category_content_se` VALUES(4, 1809, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1810, 'actual');
INSERT INTO `category_content_se` VALUES(17, 1810, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1810, 'actual');
INSERT INTO `category_content_se` VALUES(5, 1810, 'actual');
INSERT INTO `category_content_se` VALUES(0, 1865, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1865, 'actual');
INSERT INTO `category_content_se` VALUES(17, 1865, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1865, 'actual');
INSERT INTO `category_content_se` VALUES(3, 1865, 'actual');
INSERT INTO `category_content_se` VALUES(0, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(22, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(11, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(17, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(36, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(7, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1866, 'actual');
INSERT INTO `category_content_se` VALUES(0, 1874, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1874, 'actual');
INSERT INTO `category_content_se` VALUES(5, 1874, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1883, 'yellowpages');
INSERT INTO `category_content_se` VALUES(38, 1883, 'yellowpages');
INSERT INTO `category_content_se` VALUES(20, 1901, 'actual');
INSERT INTO `category_content_se` VALUES(5, 1901, 'actual');
INSERT INTO `category_content_se` VALUES(11, 1901, 'actual');
INSERT INTO `category_content_se` VALUES(17, 1901, 'actual');
INSERT INTO `category_content_se` VALUES(24, 1901, 'actual');
INSERT INTO `category_content_se` VALUES(23, 1901, 'actual');
INSERT INTO `category_content_se` VALUES(25, 1901, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(11, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(17, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(24, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(23, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(25, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(28, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(21, 1902, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1876, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1876, 'actual');
INSERT INTO `category_content_se` VALUES(3, 1876, 'actual');
INSERT INTO `category_content_se` VALUES(0, 1897, 'actual');
INSERT INTO `category_content_se` VALUES(39, 1897, 'actual');
INSERT INTO `category_content_se` VALUES(0, 1898, 'actual');
INSERT INTO `category_content_se` VALUES(39, 1898, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1811, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1811, 'actual');
INSERT INTO `category_content_se` VALUES(11, 1811, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1812, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1812, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1813, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1813, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1817, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1817, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1814, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1814, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1814, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1818, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1818, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1824, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1824, 'actual');
INSERT INTO `category_content_se` VALUES(2, 1824, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1824, 'actual');
INSERT INTO `category_content_se` VALUES(32, 1824, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1824, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1828, 'actual');
INSERT INTO `category_content_se` VALUES(17, 1828, 'actual');
INSERT INTO `category_content_se` VALUES(4, 1828, 'actual');
INSERT INTO `category_content_se` VALUES(32, 1828, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1828, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1831, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1831, 'actual');
INSERT INTO `category_content_se` VALUES(12, 1831, 'actual');
INSERT INTO `category_content_se` VALUES(5, 1831, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1831, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1831, 'actual');
INSERT INTO `category_content_se` VALUES(27, 1831, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1835, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1835, 'actual');
INSERT INTO `category_content_se` VALUES(12, 1835, 'actual');
INSERT INTO `category_content_se` VALUES(5, 1835, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1835, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1835, 'actual');
INSERT INTO `category_content_se` VALUES(3, 1835, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1837, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1837, 'actual');
INSERT INTO `category_content_se` VALUES(22, 1837, 'actual');
INSERT INTO `category_content_se` VALUES(12, 1837, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1837, 'actual');
INSERT INTO `category_content_se` VALUES(3, 1837, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1838, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1838, 'actual');
INSERT INTO `category_content_se` VALUES(4, 1838, 'actual');
INSERT INTO `category_content_se` VALUES(34, 1838, 'actual');
INSERT INTO `category_content_se` VALUES(7, 1838, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1842, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1842, 'actual');
INSERT INTO `category_content_se` VALUES(2, 1842, 'actual');
INSERT INTO `category_content_se` VALUES(22, 1842, 'actual');
INSERT INTO `category_content_se` VALUES(12, 1842, 'actual');
INSERT INTO `category_content_se` VALUES(15, 1842, 'actual');
INSERT INTO `category_content_se` VALUES(3, 1842, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1847, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1847, 'actual');
INSERT INTO `category_content_se` VALUES(21, 1847, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1848, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1848, 'actual');
INSERT INTO `category_content_se` VALUES(4, 1848, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1873, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1873, 'actual');
INSERT INTO `category_content_se` VALUES(4, 1873, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1872, 'actual');
INSERT INTO `category_content_se` VALUES(1, 1872, 'actual');
INSERT INTO `category_content_se` VALUES(38, 1872, 'actual');
INSERT INTO `category_content_se` VALUES(4, 1872, 'actual');
INSERT INTO `category_content_se` VALUES(20, 1871, 'actual');

Navigate: Previous Message• Next Message
Options: Reply• Quote


Subject
Views
Written By
Posted
Optimize subquery, replace IN() with JOIN
2202
Miron jajtic
November 15, 2016 02:37PM
Re: Optimize subquery, replace IN() with JOIN
887
Peter Brawley
November 15, 2016 03:34PM
Re: Optimize subquery, replace IN() with JOIN
984
Miron jajtic
November 15, 2016 04:25PM
Re: Optimize subquery, replace IN() with JOIN
874
Rick James
November 17, 2016 08:11PM
Re: Optimize subquery, replace IN() with JOIN
801
Peter Brawley
November 22, 2016 02:44PM
Re: Optimize subquery, replace IN() with JOIN
854
Miron jajtic
November 16, 2016 04:03AM
Re: Optimize subquery, replace IN() with JOIN
858
Peter Brawley
November 16, 2016 12:51PM
Re: Optimize subquery, replace IN() with JOIN
991
Miron jajtic
November 16, 2016 03:22PM
Re: Optimize subquery, replace IN() with JOIN
834
Peter Brawley
November 16, 2016 04:44PM
Re: Optimize subquery, replace IN() with JOIN
596
sahar gull
December 27, 2016 05:32AM
Re: Optimize subquery, replace IN() with JOIN
741
Øystein Grøvlen
November 16, 2016 01:50AM
Re: Optimize subquery, replace IN() with JOIN
782
Miron jajtic
November 16, 2016 04:01AM
Re: Optimize subquery, replace IN() with JOIN
938
Øystein Grøvlen
November 16, 2016 06:23AM


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.