我有两个简单的表:
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)
)
我有一个非常简单的查询:
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说:
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
请告诉我有没有办法修复"Using where; Using temporary; Using filesort"并提高此查询的性能?
explain select href, sum from cat_urls c join ( SELECT cu.id, sum(sil_id=4601038) as sum FROM cat_urls cu JOIN products p ON p.CatUrlId=cu.Id GROUP by cu.Id ) t on t.id = c.id
,您有什么想法? - juergen did select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL (null) (null) (null) (null) 178525398 (null) 1 PRIMARY c eq_ref PRIMARY PRIMARY 4 t.Id 1 (null) 2 DERIVED cu index PRIMARY,SIL PRIMARY 4 (null) 29754233 (null) 2 DERIVED p ref CatUrl CatUrl 4 blue_collar_logs.cu.Id 6 Using index
- Pavel