Need help optimizing SQL query

Question

MySQL

There are two tables: one with movies, the second with their schedule. It is necessary to select films, sorting by the number of sessions of this film for a certain period of time. That is, all films are displayed, even without sessions, but those with more sessions are ahead.
There are several thousand films, schedules - hundreds of thousands of records.
Now here is the query:

SELECT `m`.`id` FROM `movies` AS `m` LEFT JOIN `schedule` AS `s` ON `m`.`id` = `s`.`movie` AND `s`.`date` > '2012-11-01' AND `s`.`date` <= '2012-11-02' GROUP BY `m`.`id` ORDER BY COUNT(`s`.`movie`) DESC LIMIT 0, 10

It works for a long time, the problem is in COUNT(`s`.`movie`), which apparently is calculated for each movie separately.
Can this be optimized in some way?

PS Last time they helped, thank you, I really hope for the power of the collective mind!)

Answer 1

Try this, the syntax has not been checked, but in theory it should work.

SELECT `m`.`id` FROM `movies` AS `m` LEFT JOIN (SELECT `s`.`movie`, COUNT(`s`.`movie`) as count FROM `schedule` as `s` WHERE `s`.`date` > '2012-11-01' AND `s`.`date` <= '2012-11-02' GROUP BY `s`.`movie`) as j ON `j`.`movie` = `m`.`id` ORDER BY `j`.`count`

Answer 2

Why join here at all if you choose only m.id which is equal to s.movie for you? In order to select films without a schedule, too? IMHO, this can result in redundant data.
select count(*), movie from schedule where date>'2012-11-01' and date<='2012-11-02' order by count(*) desc limit 10
This will select all movies with a schedule. And then stupidly supplement them with a query to movies to select all the movies in general and throw out the results of the first query from them. The sum will be the desired result.

Answer 3

Try like this:

SELECT DISTINCT `m`.`id`
FROM `movies` AS `m`
LEFT JOIN (
 (
  SELECT `s`.`movie` FROM `schedule`
   WHERE `s`.`date` > '2012-11-01' AND `s`.`date` <= '2012-11-02'
) AS `s` ON ​​(
   `s`.`movie` = `m`.`id`
)
ORDER BY COUNT(`s`.`movie`) DESC
LIMIT 0, 10

Answer 4

LEFT JOIN construction must be with only one condition.
If there are multiple use WHERE