Databases
StackOverflow2013
Postsdbo
POOptimizing SQL request
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimizing SQL request
    primarykey
    data
    text
    <p>Im using PDO Mysql, and made a request to select cheapest offers for a product in my database. It works fine, only problem is it is slow (for 200 offers (and still just 25 to return)) it takes almost a second, which is a lot higher than what I aim.</p> <p>I'm no expert in SQL, so I seek for your help on this matter. Here is the request and I'll be happy to provide more info if needed :</p> <pre><code>SELECT mo.id AS id, mo.stock AS stock, mo.price AS price, mo.promotional_price AS promotional_price, mo.picture_1 AS picture_1, mo.picture_2 AS picture_2, mo.picture_3 AS picture_3, mo.picture_4 AS picture_4, mo.picture_5 AS picture_5, mo.title AS title, mo.description AS description, mo.state AS state, mo.is_new AS is_new, mo.is_original AS is_original, c.name AS name, u.id AS user_id, u.username AS username, u.postal_code AS postal_code, p.name AS country_name, ra.cache_rating_avg AS cache_rating_avg, ra.cache_rating_nb AS cache_rating_nb, GROUP_CONCAT(md.delivery_mode_id SEPARATOR ', ') AS delivery_mode_ids, GROUP_CONCAT(ri.title SEPARATOR ', ') AS delivery_mode_titles FROM mp_offer mo, catalog_product_i18n c, ref_country_i18n p, mp_offer_delivery_mode md, ref_delivery_mode r, ref_delivery_mode_i18n ri, user u LEFT JOIN mp_user_review_rating_i18n ra ON u.id = ra.user_id WHERE (mo.product_id = c.id AND mo.culture = c.culture AND mo.user_id = u.id AND u.country_id = p.id AND mo.id = md.offer_id AND md.delivery_mode_id = ri.id AND mo.culture = ri.culture) AND (mo.culture = 1 AND p.culture = 1) AND mo.is_deleted = 0 AND mo.product_id = 60 AND ((u.holiday_start IS NULL) OR (u.holiday_start = '0000-00-00') OR (u.holiday_end IS NULL) OR (u.holiday_end = '0000-00-00') OR (u.holiday_start &gt; '2012-05-03') OR (u.holiday_end &lt; '2012-05-03')) AND mo.stock &gt; 0 GROUP BY mo.id ORDER BY IF (mo.promotional_price IS NULL, mo.price, LEAST(mo.price, mo.promotional_price)) ASC LIMIT 25 OFFSET 0; </code></pre> <p>I take the offers for a particular product that have their "culture" set to 1, are not deleted, that have some stock and whose seller is not in holidays. I order by price (promotional_price when there is one).</p> <p>Is LEAST a slow function?</p> <p>Here is the output of EXPLAIN :</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c const PRIMARY,catalog_product_i18n_product,catalog_product_i18n_culture PRIMARY 8 const,const 1 "Using temporary; Using filesort" 1 SIMPLE mo ref PRIMARY,culture,is_deleted,product_id,user_id culture 4 const 3 "Using where with pushed condition" 1 SIMPLE u eq_ref PRIMARY,user_country PRIMARY 4 database.mo.user_id 1 "Using where with pushed condition" 1 SIMPLE p eq_ref PRIMARY,ref_country_i18n_culture PRIMARY 8 database.u.country_id,const 1 1 SIMPLE r ALL NULL NULL NULL NULL 3 "Using join buffer" 1 SIMPLE ra ALL NULL NULL NULL NULL 4 1 SIMPLE md ref PRIMARY,fk_offer_has_delivery_mode_delivery_mode1,fk_offer_has_delivery_mode_offer1 PRIMARY 4 database.mo.id 2 1 SIMPLE ri eq_ref PRIMARY PRIMARY 2 database.md.delivery_mode_id,const 1 </code></pre> <p>Thanks in advance for your help on optimizing this request. </p> <p>J</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USuser1372006
    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
    1. VO
      singulars
      1. POOptimizing SQL request
      1. USRomil Kumar Jain
      1. VTFavorite
    1. COAt first - show explain and your schema
      singulars
      1. POOptimizing SQL request
      1. USzerkms
    2. COStart with adding index to the JOIN ids (if not already set to PRIMARY).
      singulars
      1. POOptimizing SQL request
      1. USRobin Castlin
    3. COIt is already set to PRIMARY indeed. I edited my question with the output of explain
      singulars
      1. POOptimizing SQL request
      1. USuser1372006
 

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