How to optimize my complex MySQL query?

Question

Table

Each line represents a video that was broadcast at a specific time on a specific date. There are about 1600 videos per day.

CREATE TABLE `air_video` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL, `time` TIME NOT NULL, `duration` TIME NOT NULL, `asset_id` INT(10) UNSIGNED NOT NULL, `name` VARCHAR(100) NOT NULL, `status` VARCHAR(100) NULL DEFAULT NULL, `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE INDEX `date_2` (`date`, `time`), INDEX `date` (`date`), INDEX `status` (`status`), INDEX `asset_id` (`asset_id`) ) ENGINE=InnoDB 

Task

There are two conditions.

• Each video should be displayed no more than 24 times a day.
• Each video should rotate no more than 72 hours.

The rotation refers to the time interval between the fist and the last time the video was broadcast.

Therefore, I need to select all videos that violate these conditions, taking into account the user-specified date range.

The result should be grouped by day and by the value of asset_id (video identifier). For instance:

  date asset_id name dailyCount rotationSpan 2012-04-27 123 whatever_the_name 35 76 2012-04-27 134 whatever_the_name2 39 20 2012-04-28 125 whatever_the_name3 26 43 

Query

Now I wrote this query:

 SELECT t1.date, t1.asset_id, t1.name, (SELECT COUNT(t3.asset_id) FROM air_video AS t3 WHERE t2.asset_id = t3.asset_id AND t3.date = t1.date ) AS 'dailyCount', MIN(CONCAT(t2.date, ' ', t2.time)) AS 'firstAir', MAX(CONCAT(t2.date, ' ', t2.time)) AS 'lastAir', ROUND(TIMESTAMPDIFF( MINUTE, MIN(CONCAT(t2.date, ' ', t2.time)), MAX(CONCAT(t2.date, ' ', t2.time)) ) / 60) as 'rotationSpan' FROM air_video AS t1 INNER JOIN air_video AS t2 ON t1.asset_id = t2.asset_id WHERE t1.status NOT IN ('bumpers', 'clock', 'weather') AND t1.date BETWEEN '2012-04-01' AND '2012-04-30' GROUP BY t1.asset_id, t1.date HAVING `rotationSpan` > 72 OR `dailyCount` > 24 ORDER BY `date` ASC, `rotationSpan` DESC, `dailyCount` DESC 

Problems

• The larger the range between the days specified by the user - the more time it takes to complete the request (for the range of months it takes about 9 seconds)
• The lastAir timestamp is not the last time the video was transmitted on a specific date, but the last time it was broadcast.

Answer 1

If you need to speed up the query, you need to remove the select subsector on line 3. To still have this count, you can reattach it again in the from clause with the exact parameters that you used at the beginning. Here's how it should look:

 SELECT t1.date, t1.asset_id, t1.name, COUNT(t3.asset_id) AS 'dailyCount', MIN(CONCAT(t2.date, ' ', t2.time)) AS 'firstAir', MAX(CONCAT(t2.date, ' ', t2.time)) AS 'lastAir', ROUND(TIMESTAMPDIFF( MINUTE, MIN(CONCAT(t2.date, ' ', t2.time)), MAX(CONCAT(t2.date, ' ', t2.time)) ) / 60) as 'rotationSpan' FROM air_video AS t1 INNER JOIN air_video AS t2 ON (t1.asset_id = t2.asset_id) INNER JOIN air_video AS t3 ON (t2.asset_id = t3.asset_id AND t3.date = t1.date) WHERE t1.status NOT IN ('bumpers', 'clock', 'weather') AND t1.date BETWEEN '2012-04-01' AND '2012-04-30' GROUP BY t1.asset_id, t1.date HAVING `rotationSpan` > 72 OR `dailyCount` > 24 ORDER BY `date` ASC, `rotationSpan` DESC, `dailyCount` DESC 

Since t2 is not date bound, you are obviously looking at the whole table, not the date range.

Edit: Due to the large number of date bindings, the query still worked too slowly. Then I took a different approach. I created 3 views (which you can obviously combine into a regular query without views, but I like the query of the final result more)

- T1 -

CREATE VIEW t1 AS select date,asset_id,name from air_video where (status not in ('bumpers','clock','weather')) group by asset_id,date order by date;

- T2 -

CREATE VIEW t2 AS select t1.date,t1.asset_id,t1.name,min(concat(t2.date,' ',t2.time)) AS 'firstAir',max(concat(t2.date,' ',t2.time)) AS 'lastAir',round((timestampdiff(MINUTE,min(concat(t2.date,' ',t2.time)),max(concat(t2.date,' ',t2.time))) / 60),0) AS 'rotationSpan' from (t1 join air_video t2 on((t1.asset_id = t2.asset_id))) group by t1.asset_id,t1.date;

- T3 -

CREATE VIEW t3 AS select t2.date,t2.asset_id,t2.name,count(t3.asset_id) AS 'dailyCount',t2.firstAir,t2.lastAir,t2.rotationSpan AS rotationSpan from (t2 join air_video t3 on(((t2.asset_id = t3.asset_id) and (t3.date = t2.date)))) group by t2.asset_id,t2.date;

From there, you can simply run the following query:

 SELECT date, asset_id, name, dailyCount, firstAir, lastAir, rotationSpan FROM t3 WHERE date BETWEEN '2012-04-01' AND '2012-04-30' AND ( rotationSpan > 72 OR dailyCount > 24 ) ORDER BY date ASC, rotationSpan DESC, dailyCount DESC