当前位置: 动力学知识库 > 问答 > 编程问答 >

slow mysql query with inner join 0846 sec

问题描述:

I have another problem when i used query with INNER JOIN

this query

SELECT *

FROM `engine4_product_file` INNER JOIN

`engine4_file`

ON engine4_product_file.fid = engine4_file.id

WHERE engine4_product_file.pid IN (3347,3346,3345,3343,3342,3337) and

engine4_file.active = 1 AND

engine4_file.ext IN ('jpg','gif','png','jpeg')

and this create table engine4_product_file

 CREATE TABLE `engine4_product_file` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`fid` int(11) NOT NULL,

`pid` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `engine4_product_file` (`fid`),

KEY `pid` (`pid`)

) ENGINE=InnoDB AUTO_INCREMENT=6549 DEFAULT CHARSET=latin1

and this create table engine4_file

CREATE TABLE `engine4_file` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`uid` int(11) NOT NULL,

`name` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

`url` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,

`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

`active` int(11) NOT NULL DEFAULT '1',

`size` int(11) DEFAULT NULL,

`ext` varchar(10) DEFAULT NULL,

`folder` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=48801 DEFAULT CHARSET=latin1

this explain

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE engine4_product_file range engine4_product_file,pid pid 4 NULL 30 Using where

1 SIMPLE engine4_file eq_ref PRIMARY PRIMARY 4 akafine_social2.engine4_product_file.fid 1 Using where

网友答案:

Change your WHERE conditions

  WHERE  engine4_file.active = 1 AND
         engine4_file.ext IN ('jpg','gif','png','jpeg') AND
  engine4_product_file.pid IN (3347,3346,3345,3343,3342,3337) 

Add an index

 ALTER TABLE engine4_file ADD KEY (active,ext)
分享给朋友:
您可能感兴趣的文章:
随机阅读: