Database design optimization - MariaDB (MySQL)

[Grimlakin]

If the query is taking 20 minutes even with the database in RAM, I’d look closely at the query execution plan before investing in faster CPUs. With over 2 million users, sorting and aggregating across all projects could be the real bottleneck. I’d check the indexes, temporary tables, and whether the query is doing unnecessary full-table scans or sorting large intermediate results. Splitting it into four chunks can actually make things slower if each chunk repeats expensive work or requires additional sorting and merging. MyISAM versus InnoDB performance will also depend on the query and table structure. Have you profiled the query to see where most of those 20 minutes are being spent?

You can automate a lot of this with various DB tools. From an enterprise standpoint this is why I like mssql. Sounds like the DB needs some tuning and someone is running a DB query or stored procedure that is off the rails. Like tney are aggregating data axeoss billions of rows for something. (Lkke statements either proceeding % signs are a big offender for data aggregation. Pretty much always a full table scan even with indexes.

 

[Marees]

You can automate a lot of this with various DB tools. From an enterprise standpoint this is why I like mssql. Sounds like the DB needs some tuning and someone is running a DB query or stored procedure that is off the rails. Like tney are aggregating data axeoss billions of rows for something. (Lkke statements either proceeding % signs are a big offender for data aggregation. Pretty much always a full table scan even with indexes.

if it is sql server then a columnstore index or clustered columnstore index would work for a full table scan

not sure how it would work for Maria DB