MySQL Forums
Forum List  »  Optimizer & Parser

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');

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize subquery, replace IN() with JOIN
639
November 16, 2016 04:03AM


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.