A slow website does not automatically need a larger database server. First identify the query that is slow, how often it runs and which visitor action triggers it. Changing memory settings without that evidence can hide the problem or create a new one.
Start with the installed database version
Record the database product and version before following a tuning guide. MySQL and MariaDB are different products with different configuration options. In particular, old recommendations to enable query_cache_type or increase query_cache_size do not apply to MySQL 8: its query cache was removed. Check the official MySQL changes rather than copying an old configuration file.
Find one expensive query
- Choose a slow page or action, such as a product search. Record a repeatable example and the response time.
- Ask the database administrator to inspect slow-query information or Performance Schema for that interval. Logs can contain customer data, so keep them private.
- Check both execution time and frequency. A moderately slow query repeated hundreds of times can matter more than an occasional report.
- Inspect its execution plan with
EXPLAIN. Check which tables and indexes are used and how many rows are estimated to be examined.
Do not treat EXPLAIN ANALYZE as a passive preview: it executes the statement. Use an appropriate test environment and check the EXPLAIN documentation for the installed version before running analysis on production queries.
Test the smallest useful change
For example, a search that filters by customer and date may benefit from a suitable composite index. The column order and query pattern matter; adding separate indexes to every column is not the same solution. An index also consumes storage and adds work to writes.
Compare the query plan and elapsed time before and after a proposed change on representative data. Retrieve only the columns needed, avoid repeated application queries inside loops, and review expensive sorting or pagination. Keep a rollback for schema changes.
Check the whole request
If the query improves but the page remains slow, measure the application, network and third-party requests. Use our performance metrics guide to separate server response time from browser rendering. Review server memory only after measuring the workload and accounting for every service sharing that memory.