Optimize Join Query

[a1amattyj]

Hello,

 

Im trying to optimize the following query:

 

SELECT * FROM deals d

STRAIGHT_JOIN tariffs t on(t.tarrif_numericid = d.tarrif)

STRAIGHT_JOIN handsets h on(h.handet_numeric = d.handset)

WHERE h.manufacturer_id = '32'

AND d.cost_of_phone = '0'

AND t.cost_month > 0

ORDER BY d.monthly_cost ASC

LIMIT 30

 

Where I'm altering the manufacturer_id.

 

Showing rows 0 - 29 ( 30 total, Query took 10.1470 sec) [monthly_cost: 20.00 - 26.00]

 

Here is an explain:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE d index handset,tarrif monthly_cost 7 NULL 1633547 Using where

1 SIMPLE t ref tarrif_numericid tarrif_numericid 4 latest_cms.d.tarrif 1 Using where

1 SIMPLE h ref manufacturer_id,handet_numeric,handet_numeric_2 handet_numeric 4 latest_cms.d.handset 1 Using where

 

And the tables (omitted some of the fields that are not needed here);

 

CREATE TABLE IF NOT EXISTS `deals` (

  `deal` text NOT NULL,

  `handset` int(15) NOT NULL,

  `tarrif` int(15) NOT NULL,

  `cost_of_phone` decimal(15,2) NOT NULL,

  `totalcost` decimal(15,2) NOT NULL,

  `monthly_cost` decimal(15,2) NOT NULL,

  `popularity` int(15) NOT NULL,

  `id` int(6) NOT NULL auto_increment,

  PRIMARY KEY  (`id`),

  KEY `handset` (`handset`),

  KEY `tarrif` (`tarrif`),

  KEY `monthly_cost` (`monthly_cost`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1633548 ;

 

CREATE TABLE IF NOT EXISTS `handsets` (

  `handset_id` text NOT NULL,

  `handet_numeric` int(5) NOT NULL default '0',

  `phone_name` text NOT NULL,

  `manufacturer` text NOT NULL,

  `manufacturer_id` int(5) NOT NULL,

  KEY `manufacturer_id` (`manufacturer_id`),

  KEY `handet_numeric` (`handet_numeric`),

  KEY `handet_numeric_2` (`handet_numeric`,`manufacturer_id`),

  FULLTEXT KEY `family` (`family`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

CREATE TABLE IF NOT EXISTS `tariffs` (

  `tarrif_id` text NOT NULL,

  `tarrif_numericid` int(15) NOT NULL,

  `network_id` text NOT NULL,

  `network_numericid` int(15) NOT NULL,

  `name` text NOT NULL,

  `contract_length` int(15) NOT NULL,

  `texts` int(15) NOT NULL,

  `data` int(15) NOT NULL,

  `cost_month` decimal(15,2) NOT NULL,

  `tethering` int(1) NOT NULL default '0',

  `type` int(5) NOT NULL,

  KEY `tarrif_numericid` (`tarrif_numericid`),

  KEY `network_numericid` (`network_numericid`,`contract_length`,`anytime_mins`,`texts`,`data`,`cost_month`),

  KEY `network_numericid_2` (`network_numericid`),

  KEY `contract_length` (`contract_length`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Any help would be much appreciated.

 

Thanks!

[a1amattyj]

Bump

[fenway]

Drop the straight_join -- otherwise, you've told mysql that you're smarter than it.