Improving performance on a join queryUnexplained InnoDB timeoutsFinding rows for a specified date rangeAdding...
When blogging recipes, how can I support both readers who want the narrative/journey and ones who want the printer-friendly recipe?
Validation accuracy vs Testing accuracy
Can I make popcorn with any corn?
Why CLRS example on residual networks does not follows its formula?
How do I create uniquely male characters?
Work Breakdown with Tikz
How can the DM most effectively choose 1 out of an odd number of players to be targeted by an attack or effect?
Motorized valve interfering with button?
Can a German sentence have two subjects?
GPS Rollover on Android Smartphones
I see my dog run
What makes Graph invariants so useful/important?
whey we use polarized capacitor?
Why is the design of haulage companies so “special”?
How do we improve the relationship with a client software team that performs poorly and is becoming less collaborative?
Book about a traveler who helps planets in need
What typically incentivizes a professor to change jobs to a lower ranking university?
DOS, create pipe for stdin/stdout of command.com(or 4dos.com) in C or Batch?
Is there a familial term for apples and pears?
How is it possible for user's password to be changed after storage was encrypted? (on OS X, Android)
How to type dʒ symbol (IPA) on Mac?
How to make payment on the internet without leaving a money trail?
New order #4: World
How old can references or sources in a thesis be?
Improving performance on a join query
Unexplained InnoDB timeoutsFinding rows for a specified date rangeAdding index to large mysql tablesGetting a Deadlock,Simple query is slow on 4M-rows tableOptimizing a simple query on a large tableNeed help improving sql query performanceHow to improve query count execution with mySql replicate?MySQLDump issue with a special charactersselect MAX() from MySQL view (2x INNER JOIN) is slow
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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.
mysql performance join
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 5 more comments
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.
mysql performance join
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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 ...Goutput
– akuzminsky
Oct 14 '15 at 19:06
|
show 5 more comments
0
0
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.
mysql performance join
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
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.
mysql performance join
mysql performance join
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
edited Oct 14 '15 at 19:53
Dave Kiss
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
asked Oct 14 '15 at 14:16
Dave KissDave Kiss
1012
1012
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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 ...Goutput
– akuzminsky
Oct 14 '15 at 19:06
|
show 5 more comments
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 ...Goutput
– akuzminsky
Oct 14 '15 at 19:06
What DBMS is this?
– Aaron
Oct 14 '15 at 14:32
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
@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
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
@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
EXPLAIN SELECT ...G output– akuzminsky
Oct 14 '15 at 19:06
|
show 5 more comments
2 Answers
2
active
oldest
votes
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
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
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
add a comment |
0
- This optimizes poorly:
JOIN ( SELECT ... )
JOIN ( SELECT ... )
COUNT()(and other aggregates) are often wrong when usingJOINs. Think of it this way: First theJOINsare 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.Often this avoids both problems:
SELECT
( SELECT COUNT(*) FROM ... ) AS foo_count,
( SELECT COUNT(*) FROM ... ) AS bar_count,
...
FROM main_table ...;
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f118022%2fimproving-performance-on-a-join-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
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
add a comment |
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
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
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
add a comment |
0
0
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
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
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
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
answered Oct 14 '15 at 20:33
greenlitmysqlgreenlitmysql
33613
33613
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
add a comment |
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
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
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
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
add a comment |
0
- This optimizes poorly:
JOIN ( SELECT ... )
JOIN ( SELECT ... )
COUNT()(and other aggregates) are often wrong when usingJOINs. Think of it this way: First theJOINsare 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.Often this avoids both problems:
SELECT
( SELECT COUNT(*) FROM ... ) AS foo_count,
( SELECT COUNT(*) FROM ... ) AS bar_count,
...
FROM main_table ...;
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
add a comment |
0
- This optimizes poorly:
JOIN ( SELECT ... )
JOIN ( SELECT ... )
COUNT()(and other aggregates) are often wrong when usingJOINs. Think of it this way: First theJOINsare 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.Often this avoids both problems:
SELECT
( SELECT COUNT(*) FROM ... ) AS foo_count,
( SELECT COUNT(*) FROM ... ) AS bar_count,
...
FROM main_table ...;
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
add a comment |
0
0
0
- This optimizes poorly:
JOIN ( SELECT ... )
JOIN ( SELECT ... )
COUNT()(and other aggregates) are often wrong when usingJOINs. Think of it this way: First theJOINsare 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.Often this avoids both problems:
SELECT
( SELECT COUNT(*) FROM ... ) AS foo_count,
( SELECT COUNT(*) FROM ... ) AS bar_count,
...
FROM main_table ...;
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
- This optimizes poorly:
JOIN ( SELECT ... )
JOIN ( SELECT ... )
COUNT()(and other aggregates) are often wrong when usingJOINs. Think of it this way: First theJOINsare 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.Often this avoids both problems:
SELECT
( SELECT COUNT(*) FROM ... ) AS foo_count,
( SELECT COUNT(*) FROM ... ) AS bar_count,
...
FROM main_table ...;
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
answered Oct 21 '15 at 19:47
Rick JamesRick James
43.8k22259
43.8k22259
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f118022%2fimproving-performance-on-a-join-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 ...Goutput– akuzminsky
Oct 14 '15 at 19:06