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.

Answer 2

Analyzing this query. I confess I don't understand the performance change from LIMIT 1 to LIMIT 17. Still, the problem for your store's customers (or managers) is the slowness on LIMIT 1. So let's address that.

The question you linked was for postgreSQL, not MySQL. postgreSQL has a more sophisticated way of handling ORDER BY ... LIMIT 1 than MySQL does. And, the resolution to that problem was the adding of an appropriate compound index for the required lookup.

It looks to me like the purpose of your query is to find the low-stock or out-of-stock WooCommerce product with the largest wp_posts.ID

The LEFT JOIN to the wp_wc_product_meta_lookup table should be, and is, straightforward: the ON-condition column mentioned is its primary key. This table is, basically, WooCommerce's materialized view of numeric values like stock_quantity stored in wp_postmeta. Numeric values in wp_postmeta can't be indexed because that table stores them as text strings. Yeah. I know.

The LEFT JOIN between wp_posts and wp_postmeta follows the very common ON-condition pattern ON posts.ID = meta.post_id AND meta.meta_key = 'constant'. That ON condition is notorious for poor support by WordPress's standard indexes. More or less the entire purpose of Rick and my Index WP MySQL For Speed plugin is to provide good compound indexes in wp_postmeta to work around that problem.

How so? This is the DDL it runs to add the indexes. The most important lines for this purpose: ((There's more to it, read the linked article.)

ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);

These two indexes support the ON-condition pattern in the query. I am pretty sure that adding theses keys to postmeta will make your query more predictable and faster in performance.

If the ORDER BY post.ID DESC is a very common use case, an index could be added for that.

You could try refactoring the query (if you have control over its source) to defer the retrieval of details from the wp_posts table. Like this.

SELECT wp_posts.*, postid.low_stock_amount
 FROM (
   wp_posts.ID, 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
) postid
LEFT JOIN wp_posts ON wp_posts.ID = postid.ID

This refactoring makes your complex query sort only the wp_posts.ID value and then retrieves the posts data once it has the appropriate value in hand. Lots of WordPress core code does something similar: retrieves a list of post ID values in one query, then retrieves the post data in a second query.

And, by the way, MySQL 8 ignores SQL_NO_CACHE.