Thanks for this suggestion, and for pioneering SQLite and WordPress. I must reject your suggestion. Here’s some background.
Ari Stathopoulos’s (with other contributors including a bit from me) SQLite Database Integration Plugin is still rolling out. Experience with database bottlenecks hasn’t been accumulated yet.
Because SQLite’s indexing challenges are so different (and soooo much less hassle) than MariaDB / MySQL’s, it doesn’t make sense to extend this plugin to cover them. (No prefix indexes and no silly 191-character limit, for one thing.)
SQLite does have one problematic feature, however. InnoDB tables can get their clustered indexes replaced with ones on different columns without repopulating the tables. DROP PRIMARY KEY; ADD PRIMARY KEY (post_id, meta_key, meta_id); does the trick. But SQLite has some NOROWID mischegoss required to accomplish the same thing.
If it looks like SQLite-backed WordPress needs indexing beyond the WordPress standard, another plugin, or maybe just a wp-cli script, is the way to go. I’ll stay on top of it.
If you have SQLite performance data or other experience please let me know by posting here or by posting an issue on the Github repo.
