0

I have the following MySQL query that I am attempting to run against a set of tables prepopulated with data:

$sql = sprintf('
  SELECT video.id, video.name, video.created_at, video.duration, 
  COUNT(DISTINCT loads.id) AS loads,
  COUNT(DISTINCT plays.id) AS plays,
  COUNT(DISTINCT pauses.id) AS pauses,
  COUNT(DISTINCT completions.id) AS completions,
  COUNT(DISTINCT seeks.id) AS seeks,
  ROUND( IFNULL( AVG(seeks.percentage), 0 ), 2 ) AS average_seek_percentage,
  ROUND( IFNULL( AVG(pauses.seconds), 0 ), 2 ) AS average_pause_location,
  ( ROUND( IFNULL( AVG(load_progress.percentage), 0 ), 2 ) * 100 ) AS average_load_progress,
  ROUND(IFNULL( AVG(play_progress.seconds), 0 ), 2 ) AS average_play_progress
  FROM ' . $stats_videos . ' video
  LEFT JOIN (
    SELECT *
    FROM ' . $stats_loads  . ' loads
    WHERE loads.video_id = %3$d
    AND   loads.created_at >= "%1$s"
    AND   loads.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) loads
  ON loads.video_id = video.id
  LEFT JOIN (
    SELECT *
    FROM ' . $stats_plays  . ' plays
    WHERE plays.video_id = %3$d
    AND   plays.created_at >= "%1$s"
    AND   plays.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) plays
  ON plays.video_id = video.id
  LEFT JOIN (
    SELECT *
    FROM ' . $stats_pauses  . ' pauses
    WHERE pauses.video_id = %3$d
    AND   pauses.created_at >= "%1$s"
    AND   pauses.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) pauses
  ON pauses.video_id = video.id
  LEFT JOIN (
    SELECT *
    FROM ' . $stats_completions  . ' completions
    WHERE completions.video_id = %3$d
    AND   completions.created_at >= "%1$s"
    AND   completions.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) completions
  ON completions.video_id = video.id
  LEFT JOIN (
    SELECT *
    FROM ' . $stats_seeks  . ' seeks
    WHERE seeks.video_id = %3$d
    AND   seeks.created_at >= "%1$s"
    AND   seeks.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) seeks
  ON seeks.video_id = video.id
  LEFT JOIN (
    SELECT *
    FROM ' . $stats_load_progress  . ' load_progress
    WHERE load_progress.video_id = %3$d
    AND   load_progress.created_at >= "%1$s"
    AND   load_progress.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) load_progress
  ON load_progress.video_id = video.id
  LEFT JOIN (
    SELECT *
    FROM ' . $stats_play_progress  . ' play_progress
    WHERE play_progress.video_id = %3$d
    AND   play_progress.created_at >= "%1$s"
    AND   play_progress.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) play_progress
  ON play_progress.video_id = video.id
  WHERE video.id = %3$d;',
  $start_date,
  $end_date,
  $this->_video_id
);

With a small dataset, the query will join all of the results and return them as expected, but as soon as there are more rows to sift through, it collapses and I get 504 errors.

The schema is as follows:

--
-- Table structure for table `calendar`
--

DROP TABLE IF EXISTS `calendar`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `calendar` (
  `adate` date NOT NULL,
  PRIMARY KEY (`adate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `completions`
--

DROP TABLE IF EXISTS `completions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `completions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(11) unsigned NOT NULL,
  `video_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `completions_ibfk_1` (`session_id`),
  KEY `completions_ibfk_2` (`video_id`),
  KEY `completions_ibfk_3` (`page_id`),
  CONSTRAINT `completions_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `completions_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `completions_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `load_progress`
--

DROP TABLE IF EXISTS `load_progress`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `load_progress` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(11) unsigned NOT NULL,
  `video_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned NOT NULL,
  `seconds` float(8,3) unsigned NOT NULL,
  `duration` float(8,3) unsigned NOT NULL,
  `percentage` float(8,3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vimeography_stats_load_progress_unique` (`session_id`,`id`,`video_id`,`page_id`),
  KEY `load_progress_ibfk_2` (`video_id`),
  KEY `load_progress_ibfk_3` (`page_id`),
  CONSTRAINT `load_progress_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `load_progress_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `load_progress_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `loads`
-- 

DROP TABLE IF EXISTS `loads`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `loads` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(11) unsigned NOT NULL,
  `video_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `loads_ibfk_1` (`session_id`),
  KEY `loads_ibfk_2` (`video_id`),
  KEY `loads_ibfk_3` (`page_id`),
  CONSTRAINT `loads_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loads_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loads_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=800 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pages`
--

DROP TABLE IF EXISTS `pages`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pages` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `url` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `pauses`
--

DROP TABLE IF EXISTS `pauses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pauses` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(11) unsigned NOT NULL,
  `video_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned NOT NULL,
  `seconds` float(8,3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `pauses_ibfk_1` (`session_id`),
  KEY `pauses_ibfk_2` (`video_id`),
  KEY `pauses_ibfk_3` (`page_id`),
  CONSTRAINT `pauses_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `pauses_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `pauses_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=161 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `play_progress`
--

DROP TABLE IF EXISTS `play_progress`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `play_progress` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(11) unsigned NOT NULL,
  `video_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned NOT NULL,
  `seconds` float(8,3) unsigned NOT NULL,
  `duration` float(8,3) unsigned NOT NULL,
  `percentage` float(8,3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vimeography_stats_play_progress_unique` (`session_id`,`id`,`video_id`,`page_id`),
  KEY `play_progress_ibfk_2` (`video_id`),
  KEY `play_progress_ibfk_3` (`page_id`),
  CONSTRAINT `play_progress_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `play_progress_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `play_progress_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `plays`
--

DROP TABLE IF EXISTS `plays`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `plays` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(11) unsigned NOT NULL,
  `video_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `plays_ibfk_1` (`session_id`),
  KEY `plays_ibfk_2` (`video_id`),
  KEY `plays_ibfk_3` (`page_id`),
  CONSTRAINT `plays_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `plays_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `plays_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `seeks`
--

DROP TABLE IF EXISTS `seeks`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `seeks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` int(11) unsigned NOT NULL,
  `video_id` int(11) unsigned NOT NULL,
  `page_id` int(11) unsigned NOT NULL,
  `seconds` float(8,3) unsigned NOT NULL,
  `duration` float(8,3) unsigned NOT NULL,
  `percentage` float(8,3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vimeography_stats_seeks_unique` (`session_id`,`id`,`video_id`,`page_id`),
  KEY `seeks_ibfk_2` (`video_id`),
  KEY `seeks_ibfk_3` (`page_id`),
  CONSTRAINT `seeks_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seeks_ibfk_2` FOREIGN KEY (`video_id`) REFERENCES `videos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `seeks_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=480 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `sessions`
--

DROP TABLE IF EXISTS `sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sessions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `token` varchar(32) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=816 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `videos`
--

DROP TABLE IF EXISTS `videos`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `videos` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `uri` varchar(150) NOT NULL,
  `duration` float(8,3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uri` (`uri`)
) ENGINE=InnoDB AUTO_INCREMENT=402 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

And here is the current execution plan:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: video
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: loads
         type: ref
possible_keys: loads_ibfk_2
          key: loads_ibfk_2
      key_len: 4
          ref: const
         rows: 6
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: plays
         type: ref
possible_keys: plays_ibfk_2
          key: plays_ibfk_2
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: pauses
         type: ref
possible_keys: pauses_ibfk_2
          key: pauses_ibfk_2
      key_len: 4
          ref: const
         rows: 2
        Extra: Using where
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: completions
         type: ref
possible_keys: completions_ibfk_2
          key: completions_ibfk_2
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: seeks
         type: ref
possible_keys: seeks_ibfk_2
          key: seeks_ibfk_2
      key_len: 4
          ref: const
         rows: 14
        Extra: Using where
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: load_progress
         type: ref
possible_keys: load_progress_ibfk_2
          key: load_progress_ibfk_2
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: play_progress
         type: ref
possible_keys: play_progress_ibfk_2
          key: play_progress_ibfk_2
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where

I'm open to any suggestions on how to make this better! Though it may be installed on systems without InnoDB. I'm not very experienced on writing performant joins with calculations.

Edit

Even simplifying to the following query results in a query that takes far too long for displaying on the client side:

MariaDB [production]> SELECT video.id, video.name, video.created_at, video.duration, 
    ->       COUNT(DISTINCT loads.id) AS loads,
    ->       COUNT(DISTINCT plays.id) AS plays,
    ->       COUNT(DISTINCT pauses.id) AS pauses,
    ->       COUNT(DISTINCT completions.id) AS completions,
    ->       COUNT(DISTINCT seeks.id) AS seeks
    ->       FROM videos video
    ->       LEFT JOIN (
    ->         SELECT *
    ->         FROM loads loads
    ->         WHERE loads.video_id = 5
    ->         AND   loads.created_at >= "2015-10-07 19:29:27"
    ->         AND   loads.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
    ->       ) loads
    ->       ON loads.video_id = video.id
    ->       LEFT JOIN (
    ->         SELECT *
    ->         FROM plays plays
    ->         WHERE plays.video_id = 5
    ->         AND   plays.created_at >= "2015-10-07 19:29:27"
    ->         AND   plays.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
    ->       ) plays
    ->       ON plays.video_id = video.id
    ->       LEFT JOIN (
    ->         SELECT *
    ->         FROM pauses pauses
    ->         WHERE pauses.video_id = 5
    ->         AND   pauses.created_at >= "2015-10-07 19:29:27"
    ->         AND   pauses.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
    ->       ) pauses
    ->       ON pauses.video_id = video.id
    ->       LEFT JOIN (
    ->         SELECT *
    ->         FROM completions completions
    ->         WHERE completions.video_id = 5
    ->         AND   completions.created_at >= "2015-10-07 19:29:27"
    ->         AND   completions.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
    ->       ) completions
    ->       ON completions.video_id = video.id
    ->       LEFT JOIN (
    ->         SELECT *
    ->         FROM seeks seeks
    ->         WHERE seeks.video_id = 5
    ->         AND   seeks.created_at >= "2015-10-07 19:29:27"
    ->         AND   seeks.created_at < DATE_ADD("2015-10-14 19:29:27", INTERVAL 1 DAY)
    ->       ) seeks
    ->       ON seeks.video_id = video.id
    ->       WHERE video.id = 5;
+----+----------------------+---------------------+----------+-------+-------+--------+-------------+-------+
| id | name                 | created_at          | duration | loads | plays | pauses | completions | seeks |
+----+----------------------+---------------------+----------+-------+-------+--------+-------------+-------+
|  5 | Intro to Motorcycles | 2015-10-06 20:37:21 |  348.000 |    44 |    25 |     16 |           4 |    65 |
+----+----------------------+---------------------+----------+-------+-------+--------+-------------+-------+
1 row in set (10.75 sec)

Thanks in advance.

10
  • What DBMS is this? – Aaron Oct 14 '15 at 14:32
  • @Aaron sorry, guess that's a pretty important detail. Running on MySQL – Dave Kiss Oct 14 '15 at 14:46
  • Please share tables structure and current execution plan – akuzminsky Oct 14 '15 at 18:16
  • @akuzminsky added the schema above. what do you mean by current execution plan? – Dave Kiss Oct 14 '15 at 19:02
  • EXPLAIN SELECT ...\G output – akuzminsky Oct 14 '15 at 19:06
1
  1. This optimizes poorly:

    JOIN ( SELECT ... )
    JOIN ( SELECT ... )
    
  2. COUNT() (and other aggregates) are often wrong when using JOINs. Think of it this way: First the JOINs are done, building a temp table with lots of rows. Then the aggregates are performed. Sure, COUNT(DISTINCT ...) can partially compensate for the inflation/deflation, but the whole mess is expensive.

  3. Often this avoids both problems:

    SELECT
        ( SELECT COUNT(*) FROM ... ) AS foo_count,
        ( SELECT COUNT(*) FROM ... ) AS bar_count,
        ...
    FROM main_table ...;
    
0

You're doing a count(distinct) by passing the video_id. Do you need the left join? Regular join is much faster. You should probably use also a "group by" at the end of your query:

Group by video.id, video.name, video.created_at, video.duration

Instead of using Select * for your sub-query, I would specify the actual column name you need.

 LEFT JOIN (
    SELECT *
    FROM ' . $stats_loads  . ' loads
    WHERE loads.video_id = %3$d
    AND   loads.created_at >= "%1$s"
    AND   loads.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) loads

 LEFT JOIN (
    SELECT id
    FROM ' . $stats_loads  . ' loads
    WHERE loads.video_id = %3$d
    AND   loads.created_at >= "%1$s"
    AND   loads.created_at < DATE_ADD("%2$s", INTERVAL 1 DAY)
  ) loads
2
  • I've made the suggested changes, but the query still doesn't return results in any reasonable amount of time. There's gotta be something that is acting as a bottleneck here, as my dataset isn't even that large! – Dave Kiss Oct 14 '15 at 21:03
  • Try to add an index on each table you're joining on the following field: video_id, created_at – greenlitmysql Oct 14 '15 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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