Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using the following query in our system:

SELECT  f.type AS type, 
    COUNT(i.id) AS submissions,
    SUM((CASE (e.oiPost + e.oiPhone + e.oiEmail) 
            WHEN 0 THEN 0 
            WHEN 1 THEN 1
            WHEN 2 THEN 1
            WHEN 3 THEN 1
            ELSE 0 END))
FROM submission i
  LEFT JOIN fragment_live f ON f.ident = i.fragment_ident
  LEFT JOIN site s ON s.id = i.site_id
  LEFT JOIN entry e ON e.submission_id = i.id
WHERE i.submitted > '2012-04-01'
GROUP BY f.type;

I think for the purposes of this question, you probably won't need to know the full table structure.

However, what we're trying to do is get a total number of opt ins from the entries linked to the submissions we are already totalling. To qualify as an opt in, either oiPost, oiPhone or oiEmail needs to be 1. Not all submissions have a corresponding entry, however, so in those cases the join onto entry won't pull anything, and thus oiPost, oiPhone and oiEmail will be NULL.

The results of EXPLAIN are as follows:

+----+-------------+-------+--------+-------------------------------+-----------------------+---------+--------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                 | key                   | key_len | ref                      | rows   | Extra                                        |
+----+-------------+-------+--------+-------------------------------+-----------------------+---------+--------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | i     | ALL    | idx_submitted,idx_submitted_2 | NULL                  | NULL    | NULL                     | 392795 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                       | PRIMARY               | 4       | gmg-api.i.Fragment_ident |      1 |                                              |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                       | PRIMARY               | 4       | gmg-api.i.Site_id        |      1 | Using index                                  |
|  1 | SIMPLE      | e     | ref    | UNIQ_2B219D70528302C6         | UNIQ_2B219D70528302C6 | 5       | gmg-api.i.id             |      1 |                                              |
+----+-------------+-------+--------+-------------------------------+-----------------------+---------+--------------------------+--------+----------------------------------------------+

As it seems to be pertinent, here is the structure of the submission table:

CREATE TABLE `submission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `forename` varchar(255) DEFAULT NULL,
  `surname` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `newsletter` tinyint(1) DEFAULT NULL,
  `additionalFields` longtext COMMENT '(DC2Type:array)',
  `submitted` datetime NOT NULL,
  `Fragment_ident` int(11) DEFAULT NULL,
  `Territory_id` int(11) DEFAULT NULL,
  `Site_id` int(11) DEFAULT NULL,
  `address` longtext,
  `postcode` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_DB055AF3A86F9784` (`Fragment_ident`),
  KEY `IDX_DB055AF36BC6912C` (`Territory_id`),
  KEY `IDX_DB055AF339002FDA` (`Site_id`),
  KEY `idx_submitted_ident` (`submitted`,`Fragment_ident`),
  KEY `idx_ident_submitted` (`Fragment_ident`,`submitted`),
  KEY `idx_submitted` (`submitted`),
  KEY `idx_sub_id_ident` (`submitted`,`id`,`Fragment_ident`),
  KEY `idx_sub_ident_id` (`submitted`,`Fragment_ident`,`id`),
  CONSTRAINT `submission_ibfk_1` FOREIGN KEY (`Fragment_ident`) REFERENCES `fragment_live` (`ident`),
  CONSTRAINT `submission_ibfk_2` FOREIGN KEY (`Territory_id`) REFERENCES `territory` (`id`),
  CONSTRAINT `submission_ibfk_3` FOREIGN KEY (`Site_id`) REFERENCES `site` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=285274 DEFAULT CHARSET=utf8

I'm just wondering if there is a better way we could write this query, or otherwise optimise it?

share|improve this question

migrated from stackoverflow.com Apr 26 '12 at 11:56

This question came from our site for professional and enthusiast programmers.

    
Do you have an index on i.fragment_ident ? If not add one and run again the explain. Your query should read a number of rows lower than 392795 for table i if it will use the new index –  Octopus-Paul Apr 26 '12 at 11:43
    
Just tried adding one, but it didn't change the number of rows. Nor does having one with both 'submitted' and 'fragment_ident'. –  Dan Apr 26 '12 at 11:49
1  
The thing that really stands out to me is the tablescan (type = ALL in the explain output) on submission. So either you don't have a suitable index for submission.submitted, or MySQL estimates that a large enough percentage of rows will pass through the filter that it's cheaper to just do a tablescan. –  Matt Fenwick Apr 26 '12 at 11:50
    
I've tried a few alternative options for indexes on the submission.submitted column. There is one ASC index, and one DESC index on there currently, but neither are used. Should I perhaps be looking into using an index length so that it only uses the Year and Month parts? Would that even work? –  Dan Apr 26 '12 at 11:54
    
@Dan: There are no DESC indexes in MySQL. However you define them, all indexes are ASC. –  ypercube Apr 26 '12 at 12:06

1 Answer 1

Note: 1 + 1 + NULL = NULL

For this reason I'd change one part...

SUM(CASE
      WHEN e.oiPost  = 1 THEN 1
      WHEN e.oiPhone = 1 THEN 1
      WHEN e.oiEmail = 1 THEN 1
                         ELSE 0
    END)                                  AS opt_ins

Why all the records in the submission table are being processed, I have no idea. How many rows do you actually have that are submitted > '2012-04-01'?

share|improve this answer
    
SELECT COUNT(*) FROM submission WHERE submitted > '2012-04-01' results in 68,784 rows. –  Dan Apr 26 '12 at 12:16
    
p.s. I made the change you suggested, thanks! –  Dan Apr 26 '12 at 12:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.