MySQL slow query using filesort

Question

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?

Raymond Nijland · Accepted Answer · 2013-09-15 18:28:15Z

4

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

edited Sep 15, 2013 at 18:28

answered Sep 12, 2013 at 21:54

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Donald Macdonald Over a year ago

Thanks Raymond. This works well until I increase the number of colleges in a location from 1. This introduces again Using temporary; Using filesort. I have modified your demo (sqlfiddle.com/#!2/454b3/2)

Raymond Nijland Over a year ago

Indeed then MySQL will lose this advantage that it can use an system row and needs an temporary and filesort again.

Raymond Nijland Over a year ago

@Donald Macdonald ive updated an new possible way how you could do it check it out

Raymond Nijland Over a year ago

@Donald Macdonald ive run in the same situation as you but then with country and city relations this sqlfiddle.com/#!2/b34870/1 is how ive solved it in the end maybe you can use it as inspiration

Ning Zhu · Accepted Answer · 2015-03-23 16:19:13Z

1

Try index:

KEY student_sort(countup DESC ,updated_time DESC)

Then use STRAIGHT_JOIN and FORCE INDEX:

SELECT *
FROM student force index(student_sort) STRAIGHT_JOIN 
     college 
         ON student.student_college = college.college_id
WHERE college_location = 1
ORDER BY student.countup desc, 
         student.updated_time desc
LIMIT 15;

answered Mar 23, 2015 at 16:19

Ning Zhu

691 bronze badge

1 Comment

Fuad All Over a year ago

thanks. for me using "force index" did the trick.

Joshua Martell · Accepted Answer · 2013-09-12 12:54:29Z

0

I can't test this very easily, but try using this student_sort key:

KEYstudent_sort(student_college,countupDESC ,updated_timeDESC)

answered Sep 12, 2013 at 12:54

Joshua Martell

7,2122 gold badges33 silver badges37 bronze badges

1 Comment

Donald Macdonald Over a year ago

Thanks @Joshua. Unfortunately MySQL did not pick up the new index. I get the same results when I run an explain. I think this is because student_college is not part of the WHERE clause in this query. I am using college_location which is the college table that is joined to.

Collectives™ on Stack Overflow

MySQL slow query using filesort

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related