Optimize Mysql query to remove FILESORT

Asked

Viewed 466 times

3

I’m in the process of optimizing my web app queries. But since I don’t have much experience I can’t find a solution to remove the use of FILESORT.

Does anyone have any idea what to do to end this FILESORT? Which indices to create?

SELECT `essences`.*, `brands`.`name` as brand_name, `brands`.`sigla`
FROM (`essences`)
INNER JOIN `brands` ON `brands`.`id` = `essences`.`brand_id`
WHERE essences.published = 1
ORDER BY `essences`.`name` asc

EXPLAIN

id select_type table    type possible_keys key  key_len ref                  rows Extra
1  SIMPLE      brands   ALL  PRIMARY       -    -       -                    11   Using temporary; Using filesort
1  SIMPLE      essences ref  name          name 4       receitadiy.brands.id 69   Using index condition

Table brands

CREATE TABLE `brands` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`description` TEXT NULL,
`sigla` VARCHAR(50) NULL DEFAULT NULL,
`url` VARCHAR(250) NULL DEFAULT NULL,
`published` TINYINT(4) NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX `name` (`name`, `published`)
)

Table essences

CREATE TABLE `essences` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`brand_id` INT(10) UNSIGNED NOT NULL,
`tag` CHAR(10) NULL DEFAULT NULL,
`desc` TEXT NULL,
`alert` TINYINT(2) NULL DEFAULT NULL,
`alert_color` VARCHAR(50) NULL DEFAULT NULL,
`alert_notes` TEXT NULL,
`preco_ml` DECIMAL(10,2) NOT NULL,
`published` TINYINT(4) NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX `tag` (`tag`),
INDEX `name` (`brand_id`, `name`, `published`)
)

fiddle

mysql

  • You really need two ORDER BY ? Try to get these guys out...

  • I can only take one of the order by... but the order by Essences.name needs to be there. @Jorge B didn’t understand, has an index for Brands.id there.

  • 1

    Isolate only the name here: INDEX name (brand_id, name, published)

  • 1

    I think Mysql should do a filesort because vc indicates it should do an ORDER BY on "Essence" but it has no index for it. Try placing an index over "Essence".

  • @gmsantos if I use only INDEX name (name) the number of Rows in EXPLAIN is equal to the number of registrants in the table 'Essences'. Here is the link to the fiddle

  • http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/

  • @Jorgeb. I tried to change the query with the link suggestions but still couldn’t get rid of filesort.

  • I’m trying to figure it out too.

Show 3 more comments

2 answers

1

puts an input in the puplished so:

ALTER TABLE `test`.`essences` 
ADD INDEX `published` (`published` ASC);

And you’ll get an enhanced consult, you’ll see that the problem is ALL which is a fullscan on your table, so with Dice in Published you no longer need to give this fullscan, so it is a significant improvement in the query

    id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  essences    ref name,published  published   2   const   1   "Using where; Using filesort"
1   SIMPLE  brands  eq_ref  PRIMARY PRIMARY 4   test.essences.brand_id  1   NULL

Another thing, I did the test without using Join, in fact I never had to use Join anywhere, there are who say that Join is better than a sub-query, but in this case for example it will be really necessary? I did so the consultation:

    explain 
SELECT `essences`.*, `brands`.`name` as brand_name, `brands`.`sigla`
FROM `essences`,`brands` 
WHERE 
`brands`.`id` = `essences`.`brand_id` and
`essences`.`published` = 1
ORDER BY `essences`.`name` asc;

1

In theory, it is in need of filesort because it has no input in the order by field.

Creating an isolated tile in the column name should remove the filesort.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.