HasMeta and storage design #2

Activity

1. n4ff4h

   added

   next-genNext-gen Javaabu packagesNext-gen Javaabu packages

   featurePackage feature from the specPackage feature from the spec

   on Nov 4, 2026
2. n4ff4h

   added a commit that references this issue on Nov 5, 2026

   HasMeta, typed storage, meta types and query scopes

   ...

   86cd054
3. n4ff4h commented on Nov 5, 2026

   

   n4ff4h

   on Nov 5, 2026

   CollaboratorAuthor

   More actions

   Storage decision: a typed-columns meta table (option A)

   I benchmarked with benchmarks/storage.php. The full tables are in benchmarks/RESULTS.md.

   Setup

   • 100,000 posts with 8 meta keys each: 800k post rows. The shared table also holds 400k page rows.
   • MySQL 9.7 and SQLite 3.45, median of 7 runs, in ms.

   Options compared

   • A: the shared, polymorphic meta table with typed value columns and (metable_type, key, value_*, metable_id) indexes. I tested it with an EXISTS subquery and with id IN (subquery).
   • C: the same table per model, without metable_type.
   • B/json: a JSON column on the model's table.
   • B/gen: that JSON column plus a generated, indexed column per queried key.

   Query	A (IN) MySQL	A (IN) SQLite	C (IN) MySQL	B/json MySQL	B/json SQLite	B/gen MySQL	B/gen SQLite
   string =, selective	0.50	0.02	0.31	0.20	35	0.18	0.01
   boolean =, 10%, latest 20	24	0.80	22	0.33	0.10	0.17	0.04
   string IN, 20%, count	25	5.8	21	24	35	3.0	0.39
   int range 1% + sort	5.9	0.96	5.1	63	38	0.20	0.04
   sort the whole table, no filter	382	58	310	0.28	39	0.19	0.04
   bool AND string + sort	88	6.4	53	13	38	1.8	7.0
   datetime range 8%, count	10	4.9	9.4	8.7	44	0.93	0.54
   eager load the meta of 20 posts	0.98	0.35	0.53	0.22	0.03	0.18	0.03

   Why A

   • B/gen is the fastest everywhere, but it needs a migration on the model's table for every queried key. That rules out the things this package is for: fields defined in code, per-group fields (post types sharing one table), custom types, and package models that apps can't alter.
   • B/json without generated columns loses to A on most filters. Selective filters are 35 ms against 0.02 ms on SQLite, and ranges with a sort are 63 ms against 6 ms on MySQL.
   • A answers selective filters in under a millisecond on both databases from the covering index. Filters matching 10–20% of the table take 5–25 ms.
   • id IN (subquery) beats EXISTS. On SQLite it's 0.02 ms against 43 ms (EXISTS scans the model table), and on MySQL the two are equal. The scopes use IN.
   • C is 10–40% faster and half the size, because every row belongs to one model. It's supported as an option: set $meta_table on the model and create the table with MetaTable::columns().

   A's weak spot: sorting a whole large table by meta with no selective filter takes about 380 ms on MySQL at 100k rows, because the left join can't use the meta index for the order. The docs say so. For a key you sort large tables by, use a real column (or a generated one), and keep meta for the long tail of fields.
4. n4ff4h

   closed this as completedin c31a518on Nov 5, 2026