Improve MariaDB Execution Time

Question

I have a database with one table and three fields (id, hash, creation time).

This table got around 30 million records, the execution time for a query like

SELECT * from `table` WHERE hash='blahblahblah';

This query is taking around 40 seconds to execute on a 1vCPU 1GB Ubuntu Server with MariaDB installed no other high usage servers are installed.

Now I upgraded my server to 32GB RAM, SSD and 12 core Xeon physical server with MariaDB alone installed on Ubuntu 20.10 and still, the execution time was similar.

I tried increasing the limits in 50-server.conf file and also increased max memory in systemctl edit mariadb and also edited mysql/conf.d in innodb_buffer_pool_size

But still no luck. Is there any way I can improve my MariaDB speed to use entire CPU & RAM to run faster.

Answer 1

Always provide SHOW CREATE TABLE when asking about performance.

It sounds like you are missing INDEX(hash). (PRIMARY KEY(hash) or UNIQUE(hash), if present is such an index.)

Answer 2

This table got around 30 million records ...
SELECT * from `table` WHERE hash='blahblahblah'; ...
This query is taking around 40 seconds to execute

Is the hash column indexed?
40 seconds sounds perfectly reasonable for MariaDB to trawl sequentially through 30 million records.

BTW, if your table really is called table, then consider changing it to something else, if for no other reason than you're very, very likely to have more than one table and having a database full of table1, table2, table3, etc., will start to make your head hurt very quickly.

Also, you should never use reserved words as identifiers for tables, columns, etc.
It will come back and bite you at some point.