1.10.1: finding next sequential order number super-slow (15 min) on postmeta model with high row count

[lkraav]

INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT 8705342, '_order_number', IF( MAX( CAST( meta_value as UNSIGNED ) ) IS NULL, 1, MAX( CAST( meta_value as UNSIGNED ) ) + 1 ) FROM wp_postmeta WHERE meta_key='_order_number'

This query is currently taking 15 minutes to execute on our live server, with millions of posts, few tens of millions of postmeta rows.
What can we do to improve this query on postmeta, other than HPOS migration (it's roadmapped, but not immediate)?

EXPLAIN below:

EXPLAIN PARTITIONS
select 8705342, '_order_number', IF( MAX( CAST( meta_value as UNSIGNED ) ) IS NULL, 1, MAX( CAST( meta_value as UNSIGNED ) ) + 1 ) FROM wp_postmeta WHERE meta_key='_order_number'
___________ Sub-Part 1 ___________
Select Type: SIMPLE               
      Table: wp_postmeta          
 Partitions:                      
       Type: ref                  
 Poss. Keys: meta_key             
      Index: meta_key             
 Key Length: 1022                 
  Index Ref: const                
  Row Count: 196706               
    Special: Using index condition

[This query has been re-written to be explainable]

[OllieJones]

This query suffers from the curse of the key-value store in MySQL / MariaDB. meta_value columns have theTEXT data type, a character large object. That means they can't be completely indexed, but rather only prefix indexed. So queries that use meta_value columns (rather than queries that filter on those columns) can't exploit a covering index. So the query plan shown is the good / best one.

If this were my code, I would have figured out how to use an autoincrementing number (a/k/a SEQUENCE object in recent MariaDB, and in postgreSQL and Oracle) to generate these unique order numbers with out this scan of the table. Doing it the way mentioned in this tix is expensive, even before we start to worry about concurrency and race conditions.

(Beware, I have not seen the surrounding application code so I'm not super well informed on this situation.)

There's a robust hack to do this sequence generation in all versions of MySQL / MariaDB, written up here. https://stackoverflow.com/questions/27867980/what-is-the-difference-between-oracles-sequence-and-mysqls-auto-increment-fe/27868057#27868057

[lkraav]

There's a robust hack to do this sequence generation in all versions of MySQL / MariaDB, written up here. https://stackoverflow.com/questions/27867980/what-is-the-difference-between-oracles-sequence-and-mysqls-auto-increment-fe/27868057#27868057

This looks fairly solid, but seems to require maintaining a separate db table, correct?

And each unit / plugin that wants to have a unique sequence, would need its own table, correct?

[lkraav]

I see now this plugin has a Marketplace version with Performance Mode

https://woo.com/document/sequential-order-numbers/#section-11

I've purchased and will test it immediately.

[OllieJones]

Yes, the "robust hack" I mentioned does require a distinct table for each distinct sequence of integers. The tables don't grow large, so there's no storage penalty. A plugin that used the hack would have to create those tables.

MariaDB 10.6.1 and beyond has a built-in SEQUENCE object (like the Oracle object) that removes the need for the "robust hack". https://mariadb.com/kb/en/sequence-overview/ But many WordPress sites run on DBMS versions without that feature.