So.. here's a problem i encountered last month... i have some long queries, a lot of inner/left and right joins, some group by's and where's that work on a 12gb database. The DB has around 120 000 - 200 000 or more, entry rows, per table.
i tried: indexes, selecting only the columns that i need, even sub queries, but the performance of the query was still over 1s long,
So... how do you write optimized queries?
Gijo Varghese
A WordPress speed enthusiast
Is the DB under heavy under heavy load? Like too many writes? If so you could try 'read replicas'.
These two articles are great for optimizing MySQL:
Can you move the entire DB to Postgres? Moving it won't be that hard. Postgres have much better performance than MySQL. The syntax is almost the same, so you won't need to change a lot. If so you could also take the advantage of Citus for Postgres
Beyond a point, DB can't be optimized. DB is always a bottleneck! Is it possible to cache the results in Redis?