Why is limit 0,1 slower than limit 0, 17

Question

I'm trying to analyze why the following query is slower with LIMIT 0,1 than LIMIT 0,100

I've added SQL_NO_CACHE for testing purposes.

Query:

 SELECT 
  SQL_NO_CACHE  SQL_CALC_FOUND_ROWS wp_posts.*, 
  low_stock_amount_meta.meta_value AS low_stock_amount
FROM 
  wp_posts 
  LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup ON wp_posts.ID = wc_product_meta_lookup.product_id 
  LEFT JOIN wp_postmeta AS low_stock_amount_meta ON wp_posts.ID = low_stock_amount_meta.post_id 
  AND low_stock_amount_meta.meta_key = '_low_stock_amount' 
WHERE 
  1 = 1 
  AND wp_posts.post_type IN ('product', 'product_variation') 
  AND (
    (wp_posts.post_status = 'publish')
  ) 
  AND wc_product_meta_lookup.stock_quantity IS NOT NULL 
  AND wc_product_meta_lookup.stock_status IN('instock', 'outofstock') 
  AND (
    (
      low_stock_amount_meta.meta_value > '' 
      AND wc_product_meta_lookup.stock_quantity <= CAST(
        low_stock_amount_meta.meta_value AS SIGNED
      )
    ) 
    OR (
      (
        low_stock_amount_meta.meta_value IS NULL 
        OR low_stock_amount_meta.meta_value <= ''
      ) 
      AND wc_product_meta_lookup.stock_quantity <= 2
    )
  ) 

ORDER BY 
  wp_posts.ID DESC 
LIMIT 
  0, 1

Explains shows the exact same output

1   SIMPLE  wp_posts    index   PRIMARY,type_status_date    PRIMARY 8   NULL    27071   Using where
1   SIMPLE  low_stock_amount_meta   ref post_id,meta_key    meta_key    767 const   1   Using where
1   SIMPLE  wc_product_meta_lookup  eq_ref  PRIMARY,stock_status,stock_quantity,product_id  PRIMARY 8   woocommerce-admin.wp_posts.ID   1   Using where

The average query time is 350ms with LIMIT 0,1

The average query time is 7ms with LIMIT 0,100

The query performance gets faster starting with LIMIT 0,17

I've added another column to the order by clause as suggested in this question, but that triggers Using filesort in the explain output

Order by wp_posts.post_date, wp_posts.ID desc

1   SIMPLE  wp_posts    ALL PRIMARY,type_status_date    NULL    NULL    NULL    27071   Using where; Using filesort
1   SIMPLE  low_stock_amount_meta   ref post_id,meta_key    meta_key    767 const   1   Using where
1   SIMPLE  wc_product_meta_lookup  eq_ref  PRIMARY,stock_status,stock_quantity,product_id  PRIMARY 8   woocommerce-admin.wp_posts.ID   1   Using where

Is there a way to work around it without altering indices and why is this happening?

It's also interesting that the query time improves starting with LIMIT 0,17. I'm not sure why 17 is a magic number here.

Update 1: I just tried adding FORCE INDEX(PRIMARY) and now LIMIT 0,100 has same performance as LIMIT 0,1 smh

Answer 1

wp_postmeta has sloppy indexes; this slows down most queries involving it.

O. Jones and I have made a WordPress plugin to improve the indexing of postmeta. We detect all sorts of stuff like the presence of the Barracuda version of the InnoDB storage engine, and other MySQL arcana, and do the right thing.

The may speed up all three averages. It is likely to change the EXPLAINs.