Optimise Join Query

[a1amattyj]

Hello,

 

Is it possible anyone could hlep me out in optimising the following query?

 

 

SELECT DISTINCT *

FROM deals d

JOIN tariffs h ON ( d.tarrif = h.tarrif_numericid )

WHERE h.network_numericid =  '1'

ORDER BY d.monthly_cost

LIMIT 20

 

Produces: Showing rows 0 - 19 ( 20 total, Query took 20.4553 sec)

 

 

The explain for this query:

 

id select_type table type possible_keys key key_len ref rows Extra

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

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

 

 

Tables:

 

|------

|Column|Type|Null|Default

|------

|deal|text|No|

|handset|int(15)|No|

|tarrif|int(15)|No|

.....

|retailer|int(15)|No|

|monthly_cost|decimal(15,2)|No|

|popularity|int(15)|No|

== Table structure for table deals

 

|------

|Column|Type|Null|Default

|------

|tarrif_id|text|No|

|tarrif_numericid|int(15)|No|

|network_id|text|No|

|network_numericid|int(15)|No|

|name|text|No|

|cost_month|decimal(15,2)|No|

......

== Table structure for table tariffs

 

Deals table has 1.2m records and tariffs has 2500 records.

 

Any help would be much appreciated!

 

Thank you

[fenway]

You need an index on numericid.

 

And don't use DISTINCT.

[The Little Guy]

You need an index on numericid.

 

And don't use DISTINCT.

 

Wouldn't a index on "d.tarrif " be better... from what I see he already has an index on "h.tarrif_numericid".

[fenway]

You need an index on numericid.

 

And don't use DISTINCT.

 

Wouldn't a index on "d.tarrif " be better... from what I see he already has an index on "h.tarrif_numericid".

Actually, not at all -- mysql joins "left deep", so it won't use the left-sided table columns for index lookups -- it already has "found" those rows.