[SOLVED] Trying to speed query

[SAlexae]

Good day,

 

I am working on a retail site and want to add a list of recommendations to each item's data page.

 

The relevant tables in the MySQL database (v4.1.22) are:

 

CREATE TABLE `content` (
`item_id` smallint(5) unsigned NOT NULL default '0',
`postdate` date NOT NULL default '9999-12-31',
`removedate` date NOT NULL default '9999-12-31',
`title` char(90) NOT NULL default '',
`price` float(5,2) NOT NULL default '6.00',
PRIMARY KEY  (`item_id`)
)

CREATE TABLE `memberAccess` (
`member_id` smallint(5) unsigned NOT NULL default '0',
`item_id` smallint(5) unsigned NOT NULL default '0'
KEY `member_id` (`member_id`),
KEY `item_id` (`item_id`)
)

CREATE TABLE `contentSales` (
`item_id` smallint(6) unsigned NOT NULL default '0',
`total_sales` smallint(6) unsigned NOT NULL default '0',
`last_sale` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (`item_id`)
)

 

And here is the actual query:

SELECT content.item_id, COUNT( member_id ) AS csales, total_sales, last_sale
FROM content, memberAccess
LEFT JOIN contentSales ON content.item_id = contentSales.item_id
WHERE content.item_id = memberAccess.item_id
AND member_id IN ( SELECT member_id FROM memberAccess WHERE item_id = 4800 )
AND content.item_id != 4800
GROUP BY content.item_id
ORDER BY csales DESC , total_sales DESC , last_sale DESC 
LIMIT 10

 

The results of the query are exactly what they're supposed to be, but, depending on the item being queried, it can take up to 5 seconds to retrieve the results.  Obviously, with a large number of items and customers, instantaneous results are not going to be possible, but any ideas on speeding things up a little would be greatly appreciated.

 

Thank you

[Barand]

try replacing the subquery with a JOIN. Usually faster.

[SAlexae]

Thank you for your reply.  Your solution sounds interesting, but how could I replace the subquery with a JOIN?

 

The purpose of the subquery is to determine which customers (member_id ) have previously purchased the in question (for the purposes of my example, the item ID is 4800) so that the other purchases by those customers could be used as recommendations for people interested in item #4800.  How could this same function be served with a JOIN?

 

Not doubting your suggestion, here.  I guess I'm just not versed in MySQL enough to apply it without a little more explanation.

[corbin]

AND member_id IN ( SELECT member_id FROM memberAccess WHERE item_id = 4800 )

 

Roughly translates into

 

 

JOIN memberAccess ON item_id = 4800.

[SAlexae]

Yes!  It took me a little while to figure out how to apply what you fellows were suggesting (my MySQL inexperience showing, no doubt), but using JOIN instead of the subquery did indeed make gathering data much faster (in one example, down to 0.0003 seconds from 6.6 seconds).

 

Here is my query in its new form for anyone who is interested:

 

SELECT content.item_id, COUNT( m1.member_id ) AS csales, total_sales, last_sale
FROM content, memberAccess AS m1
JOIN memberAccess AS m2 ON m1.member_id = m2.member_id AND m1.item_id != m2.item_id AND m2.item_id = 4800
LEFT JOIN contentSales ON content.item_id = contentSales.item_id
WHERE content.item_id = memberAccess.item_id
GROUP BY content.item_id
ORDER BY csales DESC , total_sales DESC , last_sale DESC 
LIMIT 10

 

Thank you so much for your help.