Our WordPress Queries are making our initial response time around 10seconds! Which makes the site very slow. The site is fully optimized, removed unused css/js, minifed, defered & async, images optimized etc.
First I want to change all tables within our DB from myISAM to innoDB. I have made this change on all the table on our DEV site and seems there is no issues. I just want to confirm is it safe to do so?
I also have these tables: https://imgur.com/a/HVYqZn4
Is it safe to change autoload to 'no' on the top ones?
Thank you
Yes, it's safe to change WordPress's tables from MyISAM to InnoDB. (InnoDB takes a bit more disk space, so be sure you have that available on your MySQL server.)
You should also do your best to make sure your production site is running on a recent version of MySQL (8+) or MariaDB (10.2+) because software gets faster over time. Talk to your hosting provider about that if you're in doubt about how to do it.
No, it's not safe to change wp_options.autoload from "yes" to "no" on arbitrary rows. The core software, plugins, or themes that use those rows expect them to be autoloaded. If they aren't, you'll experience random breakage. And the software will probably create new autoload rows in an attempt to recover.
What CAN you do? if you formerly used certain plugins and don't anymore you can DELETE their wp_options rows. Some plugins are sloppy with their use of wp_options. But be careful, again you could break stuff.
There's a plugin called Advanced Database Cleaner. It's one among many plugins that get rid of old versions of stuff in your WordPress database. Run it, or a plugin like it, and see if you can scrub out any junk. It will make your database smaller and therefore a bit faster.
As you know there's a plugin called Query Monitor that lets you examine the MySQL queries used by your WordPress instance. I suggest you install that on your development system and use it to figure out which queries are causing slowdowns. It has a way to sort queries by how long they took. Then ask another question if you still need help.
There's a plugin called Index WP MySQL for Speed (by me and Rick James) which updates the MySQL indexes to work better with the WordPress queries that most often cause performance trouble. As a bonus, this plugin does the MyISAM -> InnoDB update for you if necessary. It may help. It certainly helps the autoloading from wp_options, and it helps with a few WooCommerce storefront rendering issues.
Finally, your site may have outgrown your hosting service. The budget services put limits on how fast they run. This lets them host more sites and make more money with the same equipment. But those limits make big sites slow.