How to optimize Wordpress with a large database (requests are executed for 7 or more seconds)?

Question

MySQL

I bought a ready-made site on Wordpress, but after deploying the database and files, I ran into the problem of very slow work, there are now 362,713 posts in the database.
When entering the main page, the following request is executed, and its execution time is approximately 7 seconds:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

And when you enter the admin panel, in general, the request is executed for 25 seconds:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_
key = '_yoast_wpseo_focuskw' ) WHERE 1=1  AND (
  wp_postmeta.post_id IS NULL
) AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0
, 10;

wp_postmeta currently has 695,632 entries.
Yes, I somehow didn’t have much to do with Wordpress, tell me, you need to try to optimize this exclusively on the CMS side, or you need to tweak something in the MySQL server configuration (I mean where exactly you need to twist more, and not the fact itself, or there , or there, it is clear that both there and there)?

Answer 1

Wordpress load?

Answer 2

Are there indexes?

Answer 3

There are several options, and all of them will not solve problems instantly.
1) Beat the face of the seller and force either the money to be returned, or tuned to normal operation. This is if the seller knew how much data will be processed on the server.
2) Independent actions. Here it is more interesting of course, especially if you are not very in the subject.
a) We transfer data / code to a local server and look for problems at the level of hardware performance. If everything works fine on the local - most likely there are not enough resources of hosting machines / virtual machines.
b) We connect caching systems. This can seriously speed up the work, but, like any solution, it has a number of disadvantages.
c) Tune the code by hand - in VP it's certainly not so simple and transparent, it's more like repairing a sewer flooded with a thick layer of ... waste.
In general - I sympathize, you have a quest)

Answer 4

but after deploying the db

Perhaps the reason is incorrect deployment.
If the backup was simple - a database dump from PMA, then you need to use the script and replace the path first, and then the domain.
In the future, use plugins for migration.
For a start it is necessary to understand who generates these requests. And in general why. Judging by _yoast_wpseo_focuskwthis, it may be from this plug . Or someone else is trying to read the data from him.
What is worth doing.
1. Optimize the database. It is better to do this with special plugins - they will also clean up the excess in the database. But be careful and safe.
2. Make sure and, if necessary, correct the encodings and table mapping type.
3. I recommend installing a plug-in for monitoring QM or another, and there you can already see in more detail what is happening where. And who is to blame.
4. Listen to the reports once and twice from the developers of the VP and use the mentioned methods and tools (they will be useful not only for the VP).