Optimization of the Mysql table and server
Good afternoon, colleagues!
There is a self-developed website. The database of the site spins on Mysql version 5.7.21-20. The database is located on the VPS.
The problem is in several tables - they have become quite large and queries take too long to execute.
For example, the log table contains more than 2.2 million records (1.1 GB). The required SELECT is performed for about 5-6 seconds. Request "simple" - no join, etc.
You need to optimize the structure of the table or the settings of Mysql, so that the query is executed in a maximum of 0.5 seconds. Better 0.05 and less.
What you can do:
- adjust mysql settings, change parameters, software version, in general, anything.
- change the type of table, index settings, etc.
- adjust the request while maintaining all the parameters (swap the conditions for example)
What you cant:
- change the logic of the program (optimize the query in terms of logic) - something that will require a strong rewrite of the program code (for example, division into several tables).
- change mysql to another engine.
Database structure, request and screenshots in the attachment. Access to the test database is ready to provide.
Offer the cost.
29.01.2022 14:01