Woocommerce Question posted in
The official Woocommerce documentation can be found here.

WooCommerce Product MySQL query is taking too long with ORDER BY

I have 25000+ products available in WooCommerce.

I am querying my products with the CUSTOM MYSQL query because I want minimal data to fetch instead of getting the whole products in an object.

The Query is working fine and it is as below:

SELECT 
        p.ID, 
        m1.meta_value as 'cut', 
        m2.meta_value as 'polish', 
        m3.meta_value as 'symmetry', 
        m4.meta_value as 'fluor_intensity', 
        m5.meta_value as 'short_name', 
        m6.meta_value as 'total_sales_price',
        m7.meta_value as 'available',
        m8.meta_value as 'lab',
        m9.meta_value as 'shape', 
        m10.meta_value as 'size', 
        m11.meta_value as 'color', 
        m12.meta_value as 'clarity', 
        m13.meta_value as 'depth_percent', 
        m14.meta_value as 'table_percent',
        m15.meta_value as 'meas_length',
        m16.meta_value as 'meas_width',
        m17.meta_value as 'meas_depth',
        m18.meta_value as 'price_per_caret',
        m19.meta_value as 'fancy_color',
        m20.meta_value as 'fancy_intensity',
        m21.meta_value as 'image',
        m22.meta_value as 'meas_ratio',
        m23.meta_value as 'treatment',
        m24.meta_value as 'culet_size'
        FROM
         wp_u8gwgg_posts p 
            LEFT JOIN wp_u8gwgg_postmeta m1 ON p.id = m1.post_id AND m1.meta_key = '_diamond_cut' 
            LEFT JOIN wp_u8gwgg_postmeta m2 ON p.id = m2.post_id AND m2.meta_key = '_diamond_polish' 
            LEFT JOIN wp_u8gwgg_postmeta m3 ON p.id = m3.post_id AND m3.meta_key = '_diamond_symmetry' 
            LEFT JOIN wp_u8gwgg_postmeta m4 ON p.id = m4.post_id AND m4.meta_key = '_diamond_fluor_intensity' 
            LEFT JOIN wp_u8gwgg_postmeta m5 ON p.id = m5.post_id AND m5.meta_key = '_diamond_shortname' 
            LEFT JOIN wp_u8gwgg_postmeta m6 ON p.id = m6.post_id AND m6.meta_key = '_price' 
            LEFT JOIN wp_u8gwgg_postmeta m7 ON p.id = m7.post_id AND m7.meta_key = '_diamond_available' 
            LEFT JOIN wp_u8gwgg_postmeta m8 ON p.id = m8.post_id AND m8.meta_key = '_diamond_lab' 
            LEFT JOIN wp_u8gwgg_postmeta m9 ON p.id = m9.post_id AND m9.meta_key = '_diamond_shape' 
            LEFT JOIN wp_u8gwgg_postmeta m10 ON p.id = m10.post_id AND m10.meta_key = '_diamond_size' 
            LEFT JOIN wp_u8gwgg_postmeta m11 ON p.id = m11.post_id AND m11.meta_key = '_diamond_color' 
            LEFT JOIN wp_u8gwgg_postmeta m12 ON p.id = m12.post_id AND m12.meta_key = '_diamond_clarity' 
            LEFT JOIN wp_u8gwgg_postmeta m13 ON p.id = m13.post_id AND m13.meta_key = '_diamond_depth_percent' 
            LEFT JOIN wp_u8gwgg_postmeta m14 ON p.id = m14.post_id AND m14.meta_key = '_diamond_table_percent' 
            LEFT JOIN wp_u8gwgg_postmeta m15 ON p.id = m15.post_id AND m15.meta_key = '_diamond_meas_length' 
            LEFT JOIN wp_u8gwgg_postmeta m16 ON p.id = m16.post_id AND m16.meta_key = '_diamond_meas_width' 
            LEFT JOIN wp_u8gwgg_postmeta m17 ON p.id = m17.post_id AND m17.meta_key = '_diamond_meas_depth' 
            LEFT JOIN wp_u8gwgg_postmeta m18 ON p.id = m18.post_id AND m18.meta_key = '_diamond_price_per_carat' 
            LEFT JOIN wp_u8gwgg_postmeta m19 ON p.id = m19.post_id AND m19.meta_key = '_diamond_fancy_color'
            LEFT JOIN wp_u8gwgg_postmeta m20 ON p.id = m20.post_id AND m20.meta_key = '_diamond_fancy_intensity'
            LEFT JOIN wp_u8gwgg_postmeta m21 ON p.id = m21.post_id AND m21.meta_key = '_knawatfibu_url'
            LEFT JOIN wp_u8gwgg_postmeta m22 ON p.id = m22.post_id AND m22.meta_key = '_diamond_meas_ratio'
            LEFT JOIN wp_u8gwgg_postmeta m23 ON p.id = m23.post_id AND m23.meta_key = '_diamond_treatment'
            LEFT JOIN wp_u8gwgg_postmeta m24 ON p.id = m24.post_id AND m24.meta_key = '_diamond_culet_size'
            WHERE p.post_type = "product" 
        ORDER BY total_sales_price+0 ASC
        LIMIT 0, 12

The real struggle occurs when I am adding, ORDER BY total_sales_price+0 ASC in my query.

So, when I am not adding the ORDER BY clause, the query is giving me results in around 0.02s
And the same query with ORDER BY is giving me results in around 9.4s

Questions:

  1. How can I just decrease the fetching records time with ORDER BY clause?
  2. Is there any way by which this query can perform better for end-users?

Thanks in Advance.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Finally, I have implemented the Lookup table concept from WooCommerce.

    You can check the doc here: https://developer.woocommerce.com/2019/04/01/performance-improvements-in-3-6/

    My search timing before was 15-20 seconds before applying the lookup table. And now it down to 1.5-3 seconds

    I just needed to change the query to:

    SELECT 
    p.ID, 
    p.cut as 'cut', 
    p.polish as 'polish', 
    p.symmetry as 'symmetry', 
    p.fluor_intensity as 'fluor_intensity', 
    p.short_name as 'short_name', 
    p.total_sales_price as 'total_sales_price',
    p.available as 'available',
    p.labshape as 'labshape',
FROM
    wp_u8gwgg_app_data p
ORDER BY total_sales_price+0 ASC
LIMIT 0, 12

    Left Joins are GONE now and all data I can Look it under 1 table.

    If anyone is looking for the same, can implement the Lookup table for speed optimization.


  2. 0

    You can filter wp_u8gwgg_posts and price first by using a sub-query and filtering the number of sorted rows. Then only left join the rest. That should limit the number of rows you need to join.

    select
   p.id,
   m1.meta_value as 'cut',
   m2.meta_value as 'polish',
   m3.meta_value as 'symmetry',
   m4.meta_value as 'fluor_intensity',
   m5.meta_value as 'short_name',
   p.total_sales_price,
   m7.meta_value as 'available',
   m8.meta_value as 'lab',
   m9.meta_value as 'shape',
   m10.meta_value as 'size',
   m11.meta_value as 'color',
   m12.meta_value as 'clarity',
   m13.meta_value as 'depth_percent',
   m14.meta_value as 'table_percent',
   m15.meta_value as 'meas_length',
   m16.meta_value as 'meas_width',
   m17.meta_value as 'meas_depth',
   m18.meta_value as 'price_per_caret',
   m19.meta_value as 'fancy_color',
   m20.meta_value as 'fancy_intensity',
   m21.meta_value as 'image',
   m22.meta_value as 'meas_ratio',
   m23.meta_value as 'treatment',
   m24.meta_value as 'culet_size' 
from
   (
      select
         aa.id,
         m6.meta_value as 'total_sales_price' 
      from
         wp_u8gwgg_posts aa 
         left join
            wp_u8gwgg_postmeta m6 
            ON aa.id = m6.post_id 
            and m6.meta_key = '_price' 
      where
         aa.post_type = "product" 
      order by
         m6.meta_value + 0 asc limit 0,
         12 
   )
   as p 
   left join
      wp_u8gwgg_postmeta m1 
      on p.id = m1.post_id 
      and m1.meta_key = '_diamond_cut' 
   left join
      wp_u8gwgg_postmeta m2 
      on p.id = m2.post_id 
      and m2.meta_key = '_diamond_polish' 
   left join
      wp_u8gwgg_postmeta m3 
      on p.id = m3.post_id 
      and m3.meta_key = '_diamond_symmetry' 
   left join
      wp_u8gwgg_postmeta m4 
      on p.id = m4.post_id 
      and m4.meta_key = '_diamond_fluor_intensity' 
   left join
      wp_u8gwgg_postmeta m5 
      on p.id = m5.post_id 
      and m5.meta_key = '_diamond_shortname' 
   left join
      wp_u8gwgg_postmeta m7 
      on p.id = m7.post_id 
      and m7.meta_key = '_diamond_available' 
   left join
      wp_u8gwgg_postmeta m8 
      on p.id = m8.post_id 
      and m8.meta_key = '_diamond_lab' 
   left join
      wp_u8gwgg_postmeta m9 
      on p.id = m9.post_id 
      and m9.meta_key = '_diamond_shape' 
   left join
      wp_u8gwgg_postmeta m10 
      on p.id = m10.post_id 
      and m10.meta_key = '_diamond_size' 
   left join
      wp_u8gwgg_postmeta m11 
      on p.id = m11.post_id 
      and m11.meta_key = '_diamond_color' 
   left join
      wp_u8gwgg_postmeta m12 
      on p.id = m12.post_id 
      and m12.meta_key = '_diamond_clarity' 
   left join
      wp_u8gwgg_postmeta m13 
      on p.id = m13.post_id 
      and m13.meta_key = '_diamond_depth_percent' 
   left join
      wp_u8gwgg_postmeta m14 
      on p.id = m14.post_id 
      and m14.meta_key = '_diamond_table_percent' 
   left join
      wp_u8gwgg_postmeta m15 
      on p.id = m15.post_id 
      and m15.meta_key = '_diamond_meas_length' 
   left join
      wp_u8gwgg_postmeta m16 
      on p.id = m16.post_id 
      and m16.meta_key = '_diamond_meas_width' 
   left join
      wp_u8gwgg_postmeta m17 
      on p.id = m17.post_id 
      and m17.meta_key = '_diamond_meas_depth' 
   left join
      wp_u8gwgg_postmeta m18 
      on p.id = m18.post_id 
      and m18.meta_key = '_diamond_price_per_carat' 
   left join
      wp_u8gwgg_postmeta m19 
      on p.id = m19.post_id 
      and m19.meta_key = '_diamond_fancy_color' 
   left join
      wp_u8gwgg_postmeta m20 
      on p.id = m20.post_id 
      and m20.meta_key = '_diamond_fancy_intensity' 
   left join
      wp_u8gwgg_postmeta m21 
      on p.id = m21.post_id 
      and m21.meta_key = '_knawatfibu_url' 
   left join
      wp_u8gwgg_postmeta m22 
      on p.id = m22.post_id 
      and m22.meta_key = '_diamond_meas_ratio' 
   left join
      wp_u8gwgg_postmeta m23 
      on p.id = m23.post_id 
      and m23.meta_key = '_diamond_treatment' 
   left join
      wp_u8gwgg_postmeta m24 
      on p.id = m24.post_id 
      and m24.meta_key = '_diamond_culet_size' 
order by
   p.total_sales_price + 0 asc
    Login or Signup to reply.
  3. 0

    Two speedups:

    Here’s what I mean by the first suggestion:

    First, let’s find the 12 items you want:

    SELECT  p2.ID, 
        m6.meta_value
    FROM wp_u8gwgg_posts p2 
    JOIN wp_u8gwgg_postmeta m6 ON p2.id = m6.post_id
                              AND m6.meta_key = '_price' 
    ORDER BY total_sales_price+0 ASC
    LIMIT 0, 12

    Note that I used JOIN, not LEFT JOIN. Don’t use LEFT unless the ‘right’ table is really optional. Having the improved index as mentioned in the link above will help even this query.

    Next, use that as a subquery to do the rest of the work:

    SELECT  x.ID, 
        m1.meta_value as 'cut', 
        m2.meta_value as 'polish', 
        m3.meta_value as 'symmetry', 
        m4.meta_value as 'fluor_intensity', 
        x.meta_value as 'short_name',          -- note
        m6.meta_value as 'total_sales_price',
        m7.meta_value as 'available',
        ...
    FROM ( ... the query above ...)
    JOIN wp_u8gwgg_posts p2 USING(ID)    -- again (to get other columns)
    LEFT JOIN ...
    LEFT JOIN ...   -- the rest of the JOINs  (skip m6)
    ORDER BY total_sales_price+0 ASC   -- yes, repeated
    -- no need for LIMIT, unless the JOINs lead to dups

    It would be much better if price were a numeric column in the posts table, but that violates the generality of WP and Woo, wherein the value(s) you want to filter on or order by are not easily accessible.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search