DB issues large sites

Every time a customer interacts with the cart, your plugin runs a query like “SELECT post_id FROM wp_postmeta WHERE meta_key=’_wcpa_product_meta’ AND (meta_value LIKE ‘%:99865;%’ OR meta_value LIKE ‘%:86966;%’ …)”.

With ~280k product/variations in our store, this takes 3–6 seconds and examines millions of rows. It’s running 300+ times a day. The root cause is that you’re storing product associations as serialized PHP inside a meta blob, so you can only search them with leading-wildcard LIKE, which can’t use any index.

As I see it, there are a few options:

  ┌─────────────────────────────────────────────────────────┬────────────────┬─────────────────────┐
  │                         Option                          │     Effort     │       Result        │   
  ├─────────────────────────────────────────────────────────┼────────────────┼─────────────────────┤
  │ Inverted postmeta — also store _wcpa_linked_to:<id>     │ Low — uses     │                     │   
  │ entries on the child products. Query becomes WHERE      │ existing       │ Fully indexable on  │ 
  │ meta_key LIKE '_wcpa_linked_to:%'.                      │ postmeta, no   │ meta_key            │   
  │                                                         │ schema change  │                     │ 
  ├─────────────────────────────────────────────────────────┼────────────────┼─────────────────────┤
  │ Structured keys — instead of _wcpa_product_meta         │                │ Uses wp_postmeta's  │   
  │ containing a serialized array, store one row per link:  │ Low            │ existing meta_key   │
  │ meta_key='_wcpa_link_99865'. Query: WHERE meta_key IN   │                │ index               │   
  │ ('_wcpa_link_99865', '_wcpa_link_86966', …).            │                │                     │   
  ├─────────────────────────────────────────────────────────┼────────────────┼─────────────────────┤
  │ Dedicated relation table — wp_wcpa_links (parent_id,    │ Medium —       │                     │   
  │ child_id) with index on child_id.                       │ migration      │ Cleanest solution   │ 
  │                                                         │ required       │                     │
  ├─────────────────────────────────────────────────────────┼────────────────┼─────────────────────┤
  │                                                         │                │ Repeat lookups      │   
  │ Cache the result — wp_cache_get() keyed on the sorted   │ Low            │ become free.        │
  │ ID tuple, with invalidation on save.                    │                │ Doesn't help first  │   
  │                                                         │                │ call.               │ 
  └─────────────────────────────────────────────────────────┴────────────────┴─────────────────────┘   

Could you either (a) add a secondary lookup index, e.g. inverted postmeta keys, so the query can hit meta_key instead of LIKE-scanning meta_value, or (b) wrap the lookup in WP object cache? Either would eliminate the issue on sites with large catalogues.

There’s no db/table index I can do on this, as you’re using LIKE % queries, and as mentioned, this makes your plugin very slow on large sites.