mysql - Where 子句减慢查询速度

Question

下面的查询搜索客户之前所做的选择并获得正确的结果。无论如何，查询应该尽可能快。在测试时我意识到 where 子句会减慢查询速度。 Hoq 我们可以解决这个问题吗？

SELECT 
customerselections.customer_id,
customerselections.selectedcompany_id,
companycampaigns.*,
companies.company_logo
FROM  customerselections

INNER JOIN companycampaigns ON companycampaigns.company_id=customerselections.selectedcompany_id  
INNER JOIN companies ON companies.company_id=customerselections.selectedcompany_id

WHERE customerselections.customer_id='$customerid' LIMIT $offset,$limit

更新:解释结果

客户选择:

id  select_type table   type    possible_keys   key key_len ref rows      Extra
 1  SIMPLE  customerselections  ALL NULL    NULL    NULL    NULL    12799999

公司事件

 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  SIMPLE  companycampaigns    ALL NULL    NULL    NULL    NULL    2000000

公司

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  companies   ALL NULL    NULL    NULL    NULL    2039500      

查询解释

|id|select_type | table   | type   | possible_keys        |           key |   key_len | ref  | rows | Extra |

|1|SIMPLE | customerselections | ref | selectedcompany_id,customer_id | customer_id | 4   | const  |  2   9 |  |

|1|SIMPLE| companycampaigns  | ref  | company_id   | company_id  | 4  | viptrio.customerselections.selectedcompany_id | 1 |  |

|1|SIMPLE| companies  | eq_ref | PRIMARY  | PRIMARY  | 4 | viptrio.customerselections.selectedcompany_id |1 |  |

更新

  CREATE TABLE IF NOT EXISTS `companies` (
  `company_id` int(11) NOT NULL auto_increment,
  `company_customerid` int(11) default NULL,
  `company_name` tinytext NOT NULL,
  `company_description` tinytext,
  `company_email` tinytext NOT NULL,
  `company_website` tinytext,
   `company_gsm` tinytext,
  `company_landline` tinytext,
  `company_fax` tinytext,
  `company_address` tinytext,
  `company_contactperson` tinytext,
  `company_businessid` smallint(11) NOT NULL,
  `company_cityid` smallint(5) NOT NULL,
  `company_countrycode` char(3) NOT NULL,
  `company_refnum` tinytext,
  `company_regdate` tinytext NOT NULL,
  `company_logo` tinytext,
  `company_keyword` tinytext,
  PRIMARY KEY  (`company_id`),
  KEY `company_cityid` (`company_cityid`),
  KEY `company_countrycode` (`company_countrycode`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2039501 ;



CREATE TABLE IF NOT EXISTS `companycampaigns` (
`campaign_id` int(11) NOT NULL auto_increment,
`company_id` int(11) NOT NULL,
`campaign_title` varchar(40) NOT NULL,
`campaign_detail` mediumtext NOT NULL,
`campaign_startdate` tinytext,
`campaign_enddate` tinytext,
`published` tinyint(1) NOT NULL default '0',
PRIMARY KEY  (`campaign_id`),
KEY `company_id` (`company_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2000001 ;



  CREATE TABLE IF NOT EXISTS `customerselections` (
  `selection_id` int(11) NOT NULL auto_increment,
  `customer_id` int(11) NOT NULL,
 `selectedcompany_id` int(11) NOT NULL,
  PRIMARY KEY  (`selection_id`),
  KEY `selectedcompany_id` (`selectedcompany_id`),
  KEY `customer_id` (`customer_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12800006 ;

Answer 1

试试这个(使用 customer_id 上的索引):

SELECT t1.customer_id, t1.selectedcompany_id,
       cc.*, c.company_logo
FROM   (SELECT customer_id, selectedcompany_id,
        FROM   customerselections 
        WHERE customer_id='$customerid' ) AS t1
INNER JOIN companycampaigns cc ON cc.company_id = t1.selectedcompany_id  
INNER JOIN companies c ON c.company_id = t1.selectedcompany_id
LIMIT $offset, $limit

我更喜欢使用表别名来提高查询的可读性。