How to speed up mysql?

Question

MySQL

Tell me how to be?
There is a table of 500 million lines.
I start sampling (I wait half a minute) I receive values.
At the same time, the percentages and the RAM do not strain (the selection takes place on the fields with indexes)
How to make Mysql use the full resources of the PC and can this affect the speed of the database? It's just that the processor load is at levels of 5% percent and that's it, and the RAM also does not twitch

Answer 1

Any questions about "accelerating mysql" must be accompanied not by unfounded statements "Fuck, use indexes, swear by mom!", But EXPLAIN OUTPUT
Without which the question basically does not make sense, and should be deleted.
Plus, it's nice to immediately roll out the result of SHOW ENGINE INNODB STATUS

Answer 2

You can use MySQLTuner to check what can be optimized in the MySQL configuration

Answer 3

It is not so important how many rows are in the table, how much their size is to talk about RAM.
500 million lines of 10 characters long can be only 5 GB, and completely fit into the RAM of almost any home computer. You can see the size of the database, correct the mysql settings.
But it would be more correct to analyze the query, it is possible to build the correct indexes by hand.

Answer 4

With such volumes, all sorts of denormalizations are often used so that there is no fullscan, you need to dig with indexes, sometimes you write queries differently, maybe you have a hundred JOINs in which the connection is not by keys, but by simple text fields or some other things, with 100k records it was imperceptible, but at 500m it became.
well, as people wrote above - profile ..
And by the way, if your select returns, for example, 100 million out of your 500, then you need to apply the limit, the network stack may already be the bottleneck there.

Answer 5

1. The fields that are being searched must be in the index. See explain and index selectivity
   
2. To reduce the search area, you can use partitioning https://dev.mysql.com/doc/refman/8.0/en/partitioni...
   
3. Look at settings in my.conf. There, by default, few system resources are allocated for the database
   
4. Well, it is desirable to have a disk ssd