CODEWITHSUNDEEP
mysql
gatisl
gatisl

Reputation: 1880

Optimize indexes to avoid using filesorting

Please help me to choose index for tables to avoid filesorting which occurs running particular query.

So, there are two tables demo_user and demo_question:

CREATE TABLE `demo_user` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `age` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `age` (`age`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE TABLE `demo_question` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `userId` INT(11) NOT NULL,
    `createdAt` DATETIME NOT NULL,
    `question` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `userId` (`userId`),
    INDEX `createdAt` (`createdAt`),
    CONSTRAINT `FK_demo_question_demo_user` FOREIGN KEY (`userId`) REFERENCES     `demo_user` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Some sample data:

INSERT INTO `demo_user` VALUES ('u1', 20);
INSERT INTO `demo_user` VALUES ('u2', 25);
INSERT INTO `demo_user` VALUES ('u3', 27);
INSERT INTO `demo_user` VALUES ('u4', 33);
INSERT INTO `demo_user` VALUES ('u5', 19);
INSERT INTO `demo_question` VALUES (2, '2014-01-19 15:17:13', 'q1');
INSERT INTO `demo_question` VALUES (3, '2014-01-19 15:17:43', 'q2');
INSERT INTO `demo_question` VALUES (5, '2014-01-19 15:17:57', 'q3');

On these tables I am trying to run following query:

select *
from demo_question q
left join demo_user u on q.userId = u.id
where u.age >= 20 and u.age <= 30
order by q.createdAt desc

Explanation of this query detects filesort while trying to sort results by q.createdAt column

+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | q     | ALL  | userId        | NULL | NULL    | NULL |    3 | Using  temporary; Using filesort |
|  1 | SIMPLE      | u     | ALL  | PRIMARY,age   | NULL | NULL    | NULL |    5 | Using  where; Using join buffer  |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

So my question: what can be done to prevent filesorting while running such query, because it slows down performance when there is larger number of rows in both tables?

Upvotes: 1

Views: 69

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179462

You already have all the indexes that could possibly be used by this query. There are two problems. First, this is definitely NOT a left join, it is an inner join, and you need to understand why that is true and it should be written that way, even though the optimizer probably realizes what you are intending (in spite of expressing it differently) which would explain why changing the query doesn't change the query plan.

The second problem is that you cannot expect the optimizer to choose a plan with a tiny data set that will be the same as would be used on a larger data set.

The optimizer makes decisions on "cost," and the cost of using an index on a tiny set of data is assumed to be relatively high... so it will forego that ootion now, but not likely later... the plan you're getting here will change as the data set changes.

Upvotes: 1

Related Questions