需要帮助优化MySQL查询
我有6张桌子:需要帮助优化MySQL查询
CREATE TABLE IF NOT EXISTS `sbpr_groups` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`active` tinyint(1) DEFAULT '0',
`dnd` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;
CREATE TABLE IF NOT EXISTS `sbpr_newsletter` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`from` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`mail` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`body` text COLLATE utf8_unicode_ci,
`attach1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`attach2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`attach3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=14;
CREATE TABLE IF NOT EXISTS `sbpr_news_groups` (
`newsletter_id` int(11) NOT NULL,
`groups` int(11) NOT NULL,
KEY `fk_sbpr_news_groups_sbpr_newsletter1` (`newsletter_id`),
KEY `fk_sbpr_news_groups_sbpr_groups1` (`groups`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `sbpr_news_recs` (
`newsletter_id` int(11) NOT NULL,
`recipients` int(11) NOT NULL,
KEY `fk_sbpr_news_recs_sbpr_newsletter1` (`newsletter_id`),
KEY `fk_sbpr_news_recs_sbpr_recipients1` (`recipients`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS `sbpr_recipients` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mail` varchar(160) DEFAULT NULL,
`date_reg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`active` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3008 ;
CREATE TABLE IF NOT EXISTS `sbpr_rec_groups` (
`rec_id` int(11) NOT NULL,
`group` int(11) NOT NULL,
KEY `fk_sbpr_rec_groups_sbpr_recipients` (`rec_id`),
KEY `fk_sbpr_rec_groups_sbpr_groups1` (`group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
有了这个foregin键:
ALTER TABLE `sbpr_news_groups`
ADD CONSTRAINT `fk_sbpr_news_groups_sbpr_groups1`
FOREIGN KEY (`groups`) REFERENCES `sbpr_groups` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sbpr_news_groups_sbpr_newsletter1`
FOREIGN KEY (`newsletter_id`) REFERENCES `sbpr_newsletter` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE `sbpr_news_recs`
ADD CONSTRAINT `fk_sbpr_news_recs_sbpr_newsletter1`
FOREIGN KEY (`newsletter_id`) REFERENCES `sbpr_newsletter` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sbpr_news_recs_sbpr_recipients1`
FOREIGN KEY (`recipients`) REFERENCES `sbpr_recipients` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE `sbpr_rec_groups`
ADD CONSTRAINT `fk_sbpr_rec_groups_sbpr_groups1`
FOREIGN KEY (`group`) REFERENCES `sbpr_groups` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_sbpr_rec_groups_sbpr_recipients`
FOREIGN KEY (`rec_id`) REFERENCES `sbpr_recipients` (`id`)
ON DELETE CASCADE ON UPDATE NO ACTION;
视觉表的结构:
我想选择所有的r OWS从sbpr_newsletter表,并从sbpr_recipients其id规定在sbpr_news_recs或规定在sbpr_rec_groups depence上FKS添加到每个这些行的行数的。
Ex。我想要选择所有收件人的通讯电子邮件都在sbpr_news_recs或存在于组中,至少在sbpr_rec_groups加上活跃收件人的数量。
我已经workinq SQL:
SELECT d.id, d.subject , d.created_date,
(SELECT count(*) FROM sbpr_recipients r
LEFT JOIN sbpr_news_recs nr ON nr.recipients = r.id
LEFT JOIN sbpr_rec_groups g ON g.rec_id = r.id
LEFT JOIN sbpr_news_groups ng ON ng.groups = g.group
WHERE nr.newsletter_id = d.id OR ng.newsletter_id = d.id) AS repicients,
(SELECT count(*) FROM sbpr_recipients r
LEFT JOIN sbpr_news_recs nr ON nr.recipients = r.id
LEFT JOIN sbpr_rec_groups g ON g.rec_id = r.id
LEFT JOIN sbpr_news_groups ng ON ng.groups = g.group
WHERE (nr.newsletter_id = d.id OR ng.newsletter_id = d.id)
AND r.active = 1) AS active_repicients
FROM sbpr_newsletter d
ORDER BY d.id ASC, d.id
解释这个SQL语句:
问题: 我如何优化SQL?
只是方法来优化,两个SELECT查询转移到JOIN子句 -
SELECT d.id
, d.subject
, d.created_date
, count(if(nr_newsletter_id is not null or ng_newsletter_id is not null, 1, null)) repicients
, count(if((nr_newsletter_id is not null or ng_newsletter_id is not null) and t.active = 1, 1, null)) active_repicients
FROM
sbpr_newsletter d
LEFT JOIN (
SELECT nr.newsletter_id nr_newsletter_id
, ng.newsletter_id ng_newsletter_id
, r.active
FROM
sbpr_recipients r
LEFT JOIN sbpr_news_recs nr
ON nr.recipients = r.id
LEFT JOIN sbpr_rec_groups g
ON g.rec_id = r.id
LEFT JOIN sbpr_news_groups ng
ON ng.groups = g.group
) t
ON nr_newsletter_id = d.id OR ng_newsletter_id = d.id
GROUP BY
d.id;
我rewrited查询了一下,它不是测试,而是尝试。
非常好!它比我的变体执行速度快两倍。 – 2011-04-14 08:25:24
对于SELECT左侧JOIN中的+1非常好。 – Johan 2011-04-14 08:33:29
我刚刚注意到您的查询结果,而我的结果不同:http://imm.io/4YWf – 2011-04-14 08:39:17
你可以创建一个视图,查询代替 - 折衷是存储,但应该大大从服务器删除加载...
收件人的子查询/ active_recipients运行两次,每次返回3311条记录,所以它作为一种观点值得定义。
否则,请在连接中使用的外键上定义索引。
来自@Devart的查询与索引同时执行,没有它们,我是否还需要添加索引? – 2011-04-14 08:32:50
+1为所有细节。希望更多的问题是这样的。 – Wes 2011-04-14 07:40:18
当你将'd.id ASC,d.id'的顺序更改为'd.id ASC'顺序时,你的解释是什么样的? – eisberg 2011-04-14 07:59:01
@eisberg http://imm.io/4YVk – 2011-04-14 08:16:24