Tables:
items
id | channel_id | title | decsiption | etc.
channels
id | url | etc.
channels_users
id | channel_id | user_id
---------------------- first ----------------------
SELECT * FROM `items` AS `Item`
LEFT JOIN `channels` AS `Channel` ON (
`Item`.`channel_id` IN (
SELECT `channel_id`
FROM `channels_users`
WHERE user_id =1)
)
ORDER BY `Item`.`created` DESC
LIMIT 20
---------------------- second ----------------------
CREATE TEMPORARY TABLE tmp_channels
SELECT Channel.id, Channel.url, Channel.updated, Item.id AS ItemId, Item.channel_id, Item.created, Item.checksum, Item.title, Item.description, Item.link, Item.date, Item.image, Item.topic, Item.latitude, Item.longitude
FROM `channels` AS `Channel`
LEFT JOIN `items` AS `Item` ON ( `Item`.`channel_id` = `Channel`.`id` )
ORDER BY `Item`.`created` DESC ;
CREATE TEMPORARY TABLE tmp_channels_users
SELECT *
FROM `channels_users`
WHERE user_id =1;
SELECT *
FROM `tmp_channels` AS `Channel`
JOIN `tmp_channels_users` ON ( `tmp_channels_users`.`user_id` =1
AND `tmp_channels_users`.`channel_id` = `Channel`.`id` )
WHERE 1 =1;
Refactorings
No refactoring yet !
morgo
October 30, 2007, October 30, 2007 22:37, permalink
No rating.
Login to rate!
Please attach the output from:
SHOW CREATE TABLE items;
SHOW CREATE TABLE channels;
SHOW CREATE TABLE channels_users;
jackbauer.myopenid.com
October 31, 2007, October 31, 2007 16:41, permalink
No rating.
Login to rate!
Yes of course........
CREATE TABLE `items` ( `id` int(11) NOT NULL auto_increment, `channel_id` int(11) NOT NULL, `created` datetime NOT NULL, `checksum` varchar(32) collate utf8_unicode_ci NOT NULL, `title` varchar(255) collate utf8_unicode_ci NOT NULL, `description` text collate utf8_unicode_ci, `link` varchar(255) collate utf8_unicode_ci default NULL, `date` datetime default NULL, `image` varchar(255) collate utf8_unicode_ci default NULL, `topic` varchar(128) collate utf8_unicode_ci default NULL, `latitude` float default NULL, `longitude` float default NULL, PRIMARY KEY (`id`), UNIQUE KEY `checksum` (`checksum`) ) ENGINE=MyISAM AUTO_INCREMENT=110165 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `channels` ( `id` int(11) NOT NULL auto_increment, `url` varchar(255) collate utf8_unicode_ci NOT NULL, `updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `url` (`url`) ) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='This table saves all RSS-channels' CREATE TABLE `channels_users` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL, `channel_id` int(11) NOT NULL, `title` varchar(64) collate utf8_unicode_ci default NULL, PRIMARY KEY (`id`), UNIQUE KEY `user_id` (`user_id`,`channel_id`) ) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Hubert Roksor
November 6, 2007, November 06, 2007 16:29, permalink
1 rating.
Login to rate!
First of all, you need an index on items.channel_id. Also, are you _sure_ about that LEFT JOIN? It doesn't make sense to try query for empty channels, that's why I made it an [INNER] JOIN.
If I didn't get your query wrong, the thing below should give the same results as your second query in a fraction of second.
CREATE INDEX channel_id ON items (channel_id); SELECT c.id, c.url, c.updated, i.id AS ItemId, i.channel_id, i.created, i.checksum, i.title, i.description, i.link, i.date, i.image, i.topic, i.latitude, i.longitude FROM channels_users cu JOIN channels c ON c.id = cu.channel_id JOIN items i ON i.channel_id = c.id WHERE cu.user_id = 1;
Limioriftit
July 26, 2010, July 26, 2010 18:18, permalink
No rating.
Login to rate!
hiljqs, http://URBANWORSHIP.ORG#799761, How To Lose Weight Fast hmegfzm
pqxptf,http://SCOREADVANCE.NET#550678,, lrjjsst
HerschelLawary
October 27, 2010, October 27, 2010 10:07, permalink
No rating.
Login to rate!
ptcug, http://bookbrowserinc.com/ property management atlanta ga, zwfdx
The first codesnippet last over 3 minutes to execute, the second one is much faster. Although o am not sure if this is the best solution, cause of tmpTable overhead!?