Need help to optimize MySQL query

Question

I have 6 tables:

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;

With this foreign keys:

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;

Visual structure of tables: enter image description here

I want to select all rows from sbpr_newsletter table, and add to each of these lines the number of rows from sbpr_recipients whose id prescribed in sbpr_news_recs or prescribed in sbpr_rec_groups depends on FKs.

Ex. I want to select count of all recipients of current newsletter wihch are in sbpr_news_recs or exists in group which are in sbpr_rec_groups plus count of active recipients.

I have working 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

Explain of this sql: enter image description here

Question: How can I optimize my sql?

What does your explain look like when you change order by d.id ASC, d.id to order by d.id ASC? — eisberg
– eisberg, Commented Apr 14, 2011 at 7:59
@ARTstudio This looks good: the "filesort" is gone. How slow is your query now? Can you do this again with EXPLAIN EXTENDED ... if your MySQL version supports that. — eisberg
– eisberg, Commented Apr 14, 2011 at 8:18
@eisberg the same 3 rows fetched in 0:00.1040 sec EXPLAIN EXTENDED: imm.io/4YVt — Arthur Halma
– Arthur Halma, Commented Apr 14, 2011 at 8:22
ditto the comment from Wes - great question with great info. A problem well explained is a problem half solved. — Ian Wood
– Ian Wood, Commented Apr 14, 2011 at 8:24

Devart · Accepted Answer · 2011-04-14 08:57:14Z

12

Just approach to optimize, two SELECT queries are transfered into JOIN clause -

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;

I rewrited your query a little, it is not tested, but try it.

edited Apr 14, 2011 at 8:57

answered Apr 14, 2011 at 8:22

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arthur Halma Over a year ago

Very nice! It executes twice faster than my variant.

Arthur Halma Over a year ago

I just noticed thet results of your query and my is different: imm.io/4YWf

Devart Over a year ago

I think some conditions should be added into COUNT functions. The rows should be counted only when nr_newsletter_id is not null or ng_newsletter_id is not null.

Ian Wood · Accepted Answer · 2011-04-14 08:24:14Z

0

You COULD create a view and query that instead - trade off is storage but should vastly remove load from server...

answered Apr 14, 2011 at 8:24

Ian Wood

6,5736 gold badges36 silver badges75 bronze badges

Comments

boisvert · Accepted Answer · 2011-04-14 08:26:37Z

0

The subquery for recipients / active_recipients runs twice, and each time returns 3311 records, so it would be worth defining as a view.

Otherwise, define indexes on the foreign keys you use in joins.

answered Apr 14, 2011 at 8:26

boisvert

3,7392 gold badges29 silver badges55 bronze badges

1 Comment

Arthur Halma Over a year ago

Query from @Devart executes in the same time with indexes and without them, did I still need to add indexes?

Collectives™ on Stack Overflow

Need help to optimize MySQL query

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related