Optimize MySQL query to remove FILESORT

Navigation
3

I'm in the process of optimizing my queries from my web app. But as I do not have much experience I can not find a solution to remove the use of FILESORT.

Does anyone have any idea what to do to stop this FILESORT? What 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

    
asked by anonymous 06.01.2015 / 14:33

2 answers

1

In theory, it needs filesort because it has no index in the order by field.

Creating an index in the name column should remove the filesort.

    
29.01.2015 / 19:07
1

puts an index on puplished like this: 

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

And you will have an improved query, you will see that the problem is ALL that is a fullscan in your table, so with the index in the published you will 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 the join, in fact I never needed to use join anywhere, some say that join is better than a sub-query, but in this case, for example, will it really be necessary? I did so the query: 

    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;
    
02.06.2015 / 13:02
Force popup on a website, after the onclick, in javascript dataTable calls a dialog for editing, but editing does not take effect. (prime faces 5.1)