Query Optimization (MySQL)

Question

Summary:

We have a page of products and users comments that may or may not exist on each product. One table holds the product details (products_master), one table determines which product will be displayed (shoppingfeed). As we loop and output each product from query #1, query #2 is executed to pull associated comments with the current product being displayed from query #1.

Problem: This all works, but it's SLOW! We are thinking if there is a way to combine this into one optimized query that we can execute once, and then loop through it... or any other ideas to make this faster.

Query #1

 SELECT shoppingfeed.action_date,
       products_master.name,
       products_master.image_url,
       products_master.pop_sku,
       products_master.group_id,
       products_master.lowest_price,
       products_master.highest_price,
       products_master.merchant,
       products_master.width,
       products_master.height
FROM   products_master,
       shoppingfeed
WHERE  ( shoppingfeed.sf_product_id = products_master.pop_sku )
ORDER  BY action_date DESC
LIMIT  #offset#, #maxrow#

Query #2

 SELECT DISTINCT comment,
                comment_id,
                comments.user_id,
                comment_date_time,
                comment_visibility,
                comments.friend_user_id,
                thread_id,
                alias,
                first_name,
                last_name,
                action_id,
                group_id,
                users.fb_resource_id,
                gender,
                acct_type,
                ascore
FROM   comments,
       users,
       products_master,
       user_relationship
WHERE  comments.sf_product_id = #feed_item.pop_sku#
       AND comments.user_id = users.user_id
       AND products_master.pop_sku = #feed_item.pop_sku#
       AND ( ( comments.user_id = user_relationship.sf_id
               AND user_relationship.user_id = #SESSION.user_id#
               AND user_relationship.relationship_status = 3 )
              OR ( comments.user_id = #session.user_id#
                   AND user_relationship.user_id = #SESSION.user_id#
                   AND user_relationship.relationship_status = 99 ) )
ORDER  BY comment_date_time ASC

Here is the view I put together

select products_master.pop_sku AS pop_sku,products_master.group_id AS group_id,products_master.name AS name,products_master.image_url AS image_url,products_master.last_updated AS last_updated,products_master.have_it_users AS have_it_users,products_master.want_it_users AS want_it_users,products_master.adults_only AS adults_only,products_master.reviewed_by AS reviewed_by,products_master.donate_needed_qty AS donate_needed_qty,products_master.inspired_users AS inspired_users,products_master.deal_users_up AS deal_users_up,products_master.deal_users_down AS deal_users_down,products_master.merchant AS merchant,products_master.merchant_logo AS merchant_logo,products_master.width AS width,products_master.height AS height,products_master.added_by AS added_by,products_master.product_category_id AS product_category_id,comments.comment AS comment,comments.comment_date_time AS comment_date_time,comments.comment_visibility AS comment_visibility,comments.friend_user_id AS friend_user_id,comments.user_id AS user_id,comments.action_id AS action_id,comments.comment_id AS comment_id,shoppingfeed.action_code AS action_code,shoppingfeed.action_date AS action_date,shoppingfeed.new_friend_id AS new_friend_id,shoppingfeed.question_id AS question_id,users.first_name AS first_name,users.last_name AS last_name,users.shopping_clout AS shopping_clout,users.gender AS gender,users.fb_resource_id AS fb_resource_id,comments.thread_id AS thread_id,users.wish_qty AS wish_qty,merchants.logo AS logo,merchants.companyname AS companyname,product_relationship.desirability AS desirability from (((((products_master join shoppingfeed on((products_master.pop_sku = shoppingfeed.sf_product_id))) join comments on((products_master.pop_sku = comments.sf_product_id))) join users on(((comments.user_id = users.user_id) and (comments.user_id = shoppingfeed.user_id)))) join product_relationship on((product_relationship.user_id = users.user_id))) join merchants on(((products_master.merchant = merchants.merchantid) and (product_relationship.sf_product_id = products_master.pop_sku)))) order by comments.comment_date_time

What happens when you try to run it as one query? Also, to help tune it, we'll need the SHOW CREATE TABLE and EXPLAIN output. — Alain Collins
– Alain Collins, Commented Sep 5, 2013 at 4:56
I am no expert, I created a view and it seems to be much faster... I am sure it's not perfect. Look above for the view I put together — user1322114
– user1322114, Commented Sep 5, 2013 at 21:18
If you want us to help optimize a query, you need to show us the table and index definitions, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We also need row counts because that can affect query optimization greatly. If you know how to do an EXPLAIN or get an execution plan, put the results in the question as well. — Andy Lester
– Andy Lester, Commented Sep 7, 2013 at 19:42

Umaydie · Accepted Answer · 2013-09-05 05:46:46Z

1

try to use 'inner join' or 'left/right join' instead of conditions in 'where' clause
check your primary key and foreign key,add it to your table columns if they refers more in join table
create index for your table columns if they refers more in join table(in fact,foreign key is one of the index)

answered Sep 5, 2013 at 5:46

Umaydie

315 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1322114 · Accepted Answer · 2013-09-07 18:04:18Z

0

I added some indexes, and made a view, now things are cooking. I am gonna experiment with a couple more indexes to make it even faster, but I am a happy camper right now.

answered Sep 7, 2013 at 18:04

user1322114

3172 silver badges11 bronze badges

Collectives™ on Stack Overflow

Query Optimization (MySQL)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related