MYSQL Join Optimization

[a1amattyj]

Hello,

 

I currently have 2 tables, a deals table and a handset table. Now, only the handset table has a reference to a manufacturer. I wish to list the top 20 DEALS for a given manufacturer. So the easiest way to do this would be to use the handset table; each deal is linked to a handset, and hence, has the manufacturer ID required to produce results. Tables below:

 

'Deals' Table - 2.3m rows

 

|Column|Type|Null|Default

|deal|text|No|

|handset|int(15)|No|

|tarrif|int(15)|No|

........

 

'Handsets' Table - 5000 rows

 

|Column|Type|Null|Default

|deal|text|No|

|handset|int(15)|No|

|handet_numeric|int(15)|No|

|tarrif|int(15)|No|

...

|popularity|int(15)|No|

|manufacturer_id|int(15)|No|

 

Here is the query I have come up with which isn't giving me fantastic results, for manufacturer ID 3, top 20.

 

	SELECT distinct *  FROM `deals` LEFT JOIN `handsets`
	ON deals.handset = handsets.handet_numeric
	WHERE `handsets`.`manufacturer_id` = '3'
	ORDER BY `deals`.`popularity` DESC
	LIMIT 20

 

This query takes around 53 seconds too! Not very well optimised.. Wondered if anyone could let me know a way to optimise it?

 

I've added an index for handsets.handet_numeric.

 

Thanks

[The Little Guy]

Give this a go:

 

select * from deals d join handsets h on(d.handset = h.handet_numeric) where h.manufacturer_id = 3 order by d.popularity desc limit 20;

 

you would want to index:

 

deals.handset

handset.handet_numeric

 

you may even want a doube index on:

handset.handet_numeric, handset.manufacturer_id

[a1amattyj]

Thanks for the help, I've implemented all the changes you have given but the speed is still an issue:

 

Showing rows 0 - 19 ( 20 total, Query took 4.4153 sec) [popularity: 84 - 80]

 

SELECT DISTINCT *

FROM deals d

JOIN handsets h ON ( d.handset = h.handet_numeric )

WHERE h.manufacturer_id =  '9'

ORDER BY d.popularity DESC

LIMIT 20

 

Thanks!

 

Give this a go:

 

select * from deals d join handsets h on(d.handset = h.handet_numeric) where h.manufacturer_id = 3 order by d.popularity desc limit 20;

 

you would want to index:

 

deals.handset

handset.handet_numeric

 

you may even want a doube index on:

handset.handet_numeric, handset.manufacturer_id

[fenway]

Show us the EXPLAIN.