Databases
StackOverflow2013
Postsdbo
POQuery Optimization (MySQL)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuery Optimization (MySQL)
    primarykey
    data
    text
    <p>Summary:</p> <p>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.</p> <p>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.</p> <p>Query #1</p> <pre><code> 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# </code></pre> <p>Query #2</p> <pre><code> 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 </code></pre> <p>Here is the view I put together</p> <blockquote> <p>select <code>products_master</code>.<code>pop_sku</code> AS <code>pop_sku</code>,<code>products_master</code>.<code>group_id</code> AS <code>group_id</code>,<code>products_master</code>.<code>name</code> AS <code>name</code>,<code>products_master</code>.<code>image_url</code> AS <code>image_url</code>,<code>products_master</code>.<code>last_updated</code> AS <code>last_updated</code>,<code>products_master</code>.<code>have_it_users</code> AS <code>have_it_users</code>,<code>products_master</code>.<code>want_it_users</code> AS <code>want_it_users</code>,<code>products_master</code>.<code>adults_only</code> AS <code>adults_only</code>,<code>products_master</code>.<code>reviewed_by</code> AS <code>reviewed_by</code>,<code>products_master</code>.<code>donate_needed_qty</code> AS <code>donate_needed_qty</code>,<code>products_master</code>.<code>inspired_users</code> AS <code>inspired_users</code>,<code>products_master</code>.<code>deal_users_up</code> AS <code>deal_users_up</code>,<code>products_master</code>.<code>deal_users_down</code> AS <code>deal_users_down</code>,<code>products_master</code>.<code>merchant</code> AS <code>merchant</code>,<code>products_master</code>.<code>merchant_logo</code> AS <code>merchant_logo</code>,<code>products_master</code>.<code>width</code> AS <code>width</code>,<code>products_master</code>.<code>height</code> AS <code>height</code>,<code>products_master</code>.<code>added_by</code> AS <code>added_by</code>,<code>products_master</code>.<code>product_category_id</code> AS <code>product_category_id</code>,<code>comments</code>.<code>comment</code> AS <code>comment</code>,<code>comments</code>.<code>comment_date_time</code> AS <code>comment_date_time</code>,<code>comments</code>.<code>comment_visibility</code> AS <code>comment_visibility</code>,<code>comments</code>.<code>friend_user_id</code> AS <code>friend_user_id</code>,<code>comments</code>.<code>user_id</code> AS <code>user_id</code>,<code>comments</code>.<code>action_id</code> AS <code>action_id</code>,<code>comments</code>.<code>comment_id</code> AS <code>comment_id</code>,<code>shoppingfeed</code>.<code>action_code</code> AS <code>action_code</code>,<code>shoppingfeed</code>.<code>action_date</code> AS <code>action_date</code>,<code>shoppingfeed</code>.<code>new_friend_id</code> AS <code>new_friend_id</code>,<code>shoppingfeed</code>.<code>question_id</code> AS <code>question_id</code>,<code>users</code>.<code>first_name</code> AS <code>first_name</code>,<code>users</code>.<code>last_name</code> AS <code>last_name</code>,<code>users</code>.<code>shopping_clout</code> AS <code>shopping_clout</code>,<code>users</code>.<code>gender</code> AS <code>gender</code>,<code>users</code>.<code>fb_resource_id</code> AS <code>fb_resource_id</code>,<code>comments</code>.<code>thread_id</code> AS <code>thread_id</code>,<code>users</code>.<code>wish_qty</code> AS <code>wish_qty</code>,<code>merchants</code>.<code>logo</code> AS <code>logo</code>,<code>merchants</code>.<code>companyname</code> AS <code>companyname</code>,<code>product_relationship</code>.<code>desirability</code> AS <code>desirability</code> from (((((<code>products_master</code> join <code>shoppingfeed</code> on((<code>products_master</code>.<code>pop_sku</code> = <code>shoppingfeed</code>.<code>sf_product_id</code>))) join <code>comments</code> on((<code>products_master</code>.<code>pop_sku</code> = <code>comments</code>.<code>sf_product_id</code>))) join <code>users</code> on(((<code>comments</code>.<code>user_id</code> = <code>users</code>.<code>user_id</code>) and (<code>comments</code>.<code>user_id</code> = <code>shoppingfeed</code>.<code>user_id</code>)))) join <code>product_relationship</code> on((<code>product_relationship</code>.<code>user_id</code> = <code>users</code>.<code>user_id</code>))) join <code>merchants</code> on(((<code>products_master</code>.<code>merchant</code> = <code>merchants</code>.<code>merchantid</code>) and (<code>product_relationship</code>.<code>sf_product_id</code> = <code>products_master</code>.<code>pop_sku</code>)))) order by <code>comments</code>.<code>comment_date_time</code></p> </blockquote>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1322114
    1. USuser1322114
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat 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.
      singulars
      1. POQuery Optimization (MySQL)
      1. USAlain Collins
    2. COI 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
      singulars
      1. POQuery Optimization (MySQL)
      1. USuser1322114
    3. COIf 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.
      singulars
      1. POQuery Optimization (MySQL)
      1. USAndy Lester
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload