Fix Use where; Use of temporary; Using filesort

Question

I have two simple tables:

CREATE TABLE cat_urls ( Id int(11) NOT NULL AUTO_INCREMENT, SIL_Id int(11) NOT NULL, SiteId int(11) NOT NULL, AsCatId int(11) DEFAULT NULL, Href varchar(2048) NOT NULL, ReferrerHref varchar(2048) NOT NULL DEFAULT '', AddedOn datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, GroupId int(11) DEFAULT NULL, PRIMARY KEY (Id), INDEX SIL (SIL_Id, AsCatId) ) CREATE TABLE products ( Id int(11) NOT NULL AUTO_INCREMENT, CatUrlId int(11) NOT NULL, Href varchar(2048) NOT NULL, SiteIdentity varchar(2048) NOT NULL, Price decimal(12, 2) NOT NULL, IsAvailable bit(1) NOT NULL, ClientCode varchar(256) NOT NULL, PRIMARY KEY (Id), INDEX CatUrl (CatUrlId) ) 

And I have a pretty simple query:

 SELECT cu.Href, COUNT(p.CatUrlId) FROM cat_urls cu JOIN products p ON p.CatUrlId=cu.Id WHERE sil_id=4601038 GROUP by cu.Id 

EXPLAIN says:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE cu ref PRIMARY,SIL SIL 4 const 303 Using where; Using temporary; Using filesort 1 SIMPLE p ref CatUrl CatUrl 4 blue_collar_logs.cu.Id 6 Using index 

Please tell me if there is a way to fix "Use where: use temporary files using filesort" and improve the performance of this request?

Answer 1

It seems that for some reason, MySQL chooses to use the SIL index in the first table and uses it both for searching ( WHERE sil_id = 4601038 ) and for grouping ( GROUP BY cu.Id ).

You can say to use PK tables

 SELECT cu.Href, COUNT(p.CatUrlId) FROM cat_urls cu USE INDEX FOR JOIN (PRIMARY) JOIN products p ON p.CatUrlId=cu.Id WHERE sil_id=4601038 GROUP by cu.Id 

and he will execute this execution plan:

 id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ---+-------------+-------+-------+---------------+---------+---------+------------------+------+------------- 1 | SIMPLE | cu | index | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where 1 | SIMPLE | p | ref | CatUrl | CatUrl | 4 | cbs-test-1.cu.Id | 1 | Using index 

Ignore the values ​​specified in the rows column ; they are wrong because my tables are empty.

Note that the Extra column now only contains Using where , but also notices that the type join column has changed from ref (very good) to index (full index scan, not very good).

The best solution is to add an index to the SIL_Id column. I know SIL_Id is the SIL(SIL_Id, AsCatId) index prefix SIL(SIL_Id, AsCatId) , and theoretically another index in the SIL_Id column SIL_Id completely useless. But it seems to solve the problem in this case.

 ALTER TABLE cat_urls ADD INDEX (SIL_Id) ; 

Now use it in the query:

 SELECT cu.Href, COUNT(p.CatUrlId) FROM cat_urls cu USE INDEX FOR JOIN (SIL_Id) JOIN products p ON p.CatUrlId=cu.Id WHERE sil_id=4601038 GROUP by cu.Id 

Now the query execution plan looks much better:

 id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ---+-------------+-------+------+---------------+--------+---------+------------------+------+------------- 1 | SIMPLE | cu | ref | SIL_Id | SIL_Id | 4 | const | 1 | Using where 1 | SIMPLE | p | ref | CatUrl | CatUrl | 4 | cbs-test-1.cu.Id | 1 | Using index 

The downside is that we have an additional index that is (theoretically) useless. It takes up storage space and consumes processor cycles each time a row is added, deleted, or the SIL_Id field is SIL_Id .