MySQL slow query using filesort

I have speed problems with a MySQL query. The tables definitions are as follows:

CREATE TABLE IF NOT EXISTS `student` (
  `student_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `forename` varchar(30) NOT NULL,
  `updated_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `surname` varchar(50) NOT NULL,
  `student_college` int(11) DEFAULT NULL,
  `countup` smallint(5) unsigned DEFAULT NULL, 
  PRIMARY KEY (`student_id`),
  KEY `countup` (`countup`),
  KEY `student_sort` (`countup`,`updated_time`),
  KEY `student_college` (`student_college`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE IF NOT EXISTS `college` (
  `college_id` int(11) NOT NULL AUTO_INCREMENT,
  `college_name` varchar(100) NOT NULL DEFAULT 'Centre Name',
  `college_location` int(11) DEFAULT NULL,
  PRIMARY KEY (`college_id`),
  KEY `college_location` (`college_location`),
  KEY `college_name` (`college_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And the query is as follows:

SELECT *
FROM student
JOIN college ON student.student_college = college.college_id
WHERE  
college_location = 1
ORDER BY student.countup desc, student.updated_time desc
LIMIT 15;

And I get the following with an Explain:

id select_type table   type possible_keys              key              key_len    ref                           rows Extra
1  SIMPLE      college ref  "PRIMARY,college_location" college_location 5          const                         915  Using where; Using temporary; Using filesort
1  SIMPLE      student ref  student_college            student_college  5          speed_test.college.college_id 50   Using where

The Student table has got about 500,000 records and the college table has 915 rows. A third table is used to hold all the locations of the colleges. My query needs to retrieve all the students for a particular location and then sort the results by countup and updated_time. I have a composite index on countup and updated_time. I would like to get rid of the filesort but I have not been able to find a satisfactory method.

I have considered moving the college_location into the student table so that it can be combined into a composite index. Is there a better solution?

Solution

Query below will remove Using temporary; Using filesort. from the explain so this should run better in thoery..

MySQL optimizer is dumb so the trick is to force the optimizer want you want and that is an derived table based on college.college_location = 1. So you can INNER JOIN the result with the student table. And this way MySQL can use the sort key

SELECT 
 *
FROM 
 student
INNER JOIN (
    SELECT 
     college_id
    FROM 
     college
    WHERE
     college.college_location = 1  
  ) college
ON student.student_college = college.college_id
ORDER BY
    student.countup DESC
  , student.updated_time DESC

Note the new index in caps lock

See demo http://sqlfiddle.com/#!2/05c8a/1

Or you can use this if you think it makes more sense or is easier to read. The performance should be the same because the explain explained to me that it is the same.

SELECT 
 * 
FROM (
  SELECT 
    college_id
  FROM 
    college
  WHERE
    college.college_location = 1  
) 
  college

INNER JOIN
 student 

ON
 student.student_college = college.college_id

ORDER BY
    student.countup DESC
  , student.updated_time DESC

see demo http://sqlfiddle.com/#!2/05c8a/23

New strategy divide and conquer method Fire more querys to the database what will make use off correct indexes. And remove the need for an temporary table and filesort.

SET @college_ids = NULL; 

SELECT
  GROUP_CONCAT(college_id)
FROM
  college
WHERE
  college_location = 1
GROUP BY
  college_location ASC
INTO @college_ids;

SELECT 
 *
FROM 
 student
WHERE 
 student.student_college IN(@college_ids)
ORDER BY
    student.countup DESC
  , student.updated_time DESC
;

see demo http://sqlfiddle.com/#!2/454b3/61