Advices for MYSQL optimization with heavy tables


We have a cryptocurrency platform made with PHP (Laravel) and MYSQL, we had some MYSQL problems on last months because of our big tables (1million+ rows). Seems that MYSQL can't work with so many rows (if I have a "SELECT * LIMIT 10" with 10 users at the same time the MYSQL never respond any request).

Sounds like a table without indexes …

We fixed those problems by splitting those rows in two tables, one that is like a "backup" and one that is constantly used (with just some rows).

Umm … seriously? That’s unnecessary …

But for now we're facing a problem because our main table "Users" is being heavy (300,000 rows) and I don't think we can split this table, also don't think this is the best way to work.

300K is not “heavy”. It’s a tiny table compared to what MySQL can handle …

This is our first project and we do not have much knowledge on database technology, can you please give us some advice on how to solve this kind of problem? Should we migrate to other database like Postgree?

PostgreSQL can be misconfigured and misused just like MySQL. This doesn’t sound like a problem with the engine.

Our server is a aws 96core 185GB ram and it never hit 100% usage.

Oh … my … goodness. That’s … immense.

Why is it that crypto people have scads of cash to blow and little brains to solve problems with?