Optimisation of 7.3 db update queries

Thanks for the feedback, which particular query were you looking at?

ewww_images ALTER TABLE queries if I recall correctly.

Unfortunately, that doesn’t narrow things down much. If you’re able to dig up any info on the exact queries, or if you see the same thing in the next release, do let me know!

There’s only one “really expensive” query that I’m aware of, and we already defer that one post-update. Doesn’t mean there aren’t more, but nothing else is jumping out at me currently ??

Will do ??

Hi,

I cound’t find those queries, but with the new update there are new table lock queries. I have more than 3M records in ewwwio_image table.

There are multiple table lock queries generated after plugin update, but the first 2 took a quite some time to finish.

3 queries, more than 300s each
SELECT COUNT(*) FROM wp_ewwwio_images

Multiple bathes with 25-30 queries, 200-300s for each batch
UPDATE wp_ewwwio_images SET updated = '1971-01-01 00:00:00' WHERE updated < '1001-01-01 00:00:01'

Multiple bathes with these 2 queries, 5-10 minutes for all of them to finish

ALTER TABLE wp_ewwwio_images ADD COLUMN webp_size int unsigned

ALTER TABLE wp_ewwwio_images ADD COLUMN webp_error tinyint unsigne

Ah, that’s good info, and I suspect the timestamp update might be the root of it all. I can’t imagine adding a column should take long in MySQL. It certainly doesn’t on my site with 250k records (about a second or so).

I think if we can limit that to only run once (ever), the ALTER TABLE queries should complete much quicker. Even though, given a busy site like yours, the UPDATE query might still run multiple times, at least its a one time occurrence, and not on every upgrade.

Either way, I’ll do some testing and see if it’s worth putting into a separate request like we’ve done for a couple other updates.