When the clause slows down the query

advertisements

The query below searches the selections that customer did before and gets the correct results. Anyway, The query should be as fast as possible. While testing I realized where clause slows down the query. Hoq can we solve this problem?

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

UPDATE: explain results

customer selections:

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

companycampaigns

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

Companies

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

QUERY EXPLAIN

|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 |  |

UPDATE

  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 ;


Try this (with an index on 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

I prefer using table alias to make queries more readable.