Optimize/Speed Up Query

[a1amattyj]

Hello,

 

I'm trying to speed up a search function. Here is an example query:

 

SELECT * FROM deals d JOIN tariffs h on(d.tarrif = h.tarrif_numericid)

WHERE h.anytime_mins <= '1500'

AND h.texts <= '1000'

AND ( h.network_numericid = '2' OR h.network_numericid = '3')

AND h.cost_month != 0.00

ORDER BY h.cost_month

LIMIT 30;

 

Showing rows 0 - 29 ( 30 total, Query took 8.8831 sec) [cost_month: 5.11 - 10.00]

 

Deals Table:

 

deal | tarrif 
text | int(15)

 

Tariffs Table:

 

tarrif_numericid  | network_numericid | contract_length | anytime_mins | texts | data | cost_month | someother coumns..
int(15) | int(15) | int(15) | int(15) | int(15) | int(15) | decimal(15,2)	 | ... 

 

Each of the coumns above depends on what the user searches for, please see query above for example.

 

Here are my current indexes:

 

1) h.tarrif_numericid

2) h.contract_length h.anytime_mins h.texts h.data h.cost_month

3) d.tarrif

 

Explain:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE d ALL tarrif NULL NULL NULL 1241496 Using temporary; Using filesort

1 SIMPLE h ref tarrif_numericid,network_numericid tarrif_numericid 4 latest_cms.d.tarrif 1 Using where

 

Any help would be greatly appreciated increasing the speed (from the current 8s)!

 

[fenway]

Not sure why mysql wants to scan tariff table first.... seems wrong.  Try using STRAIGHT_JOIN.

[a1amattyj]

Thanks for the response, here are the new outputs:

 

SELECT * FROM deals d STRAIGHT_JOIN tariffs h on(d.tarrif = h.tarrif_numericid) WHERE h.anytime_mins != '-1' AND h.texts <= '1500' AND ( h.network_numericid = '2' OR h.network_numericid = '3' OR h.network_numericid = '6' ) AND h.cost_month != 0.00 ORDER BY h.cost_month LIMIT 30

 

Showing rows 0 - 29 ( 30 total, Query took 9.2600 sec) [cost_month: 5.11 - 10.00]

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE d ALL tarrif NULL NULL NULL 1241496 Using temporary; Using filesort

1 SIMPLE h ref tarrif_numericid,network_numericid tarrif_numericid 4 latest_cms.d.tarrif 1 Using where

 

Thanks

[fenway]

Sorry, switch the order of the tables.

[a1amattyj]

Thank you very much, that done it!

[fenway]

Thank you very much, that done it!

That means something is wrong, through -- have you tried to run ANALYZE TABLE, and then remove the STRAIGHT_JOIN?