I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began to take minutes to load (pkey1 is a DATETIME, pkey2 and pkey3 are int(11)) :
SELECT * FROM table WHERE pkey2 = ... AND pkey3= ... AND HOUR(pkey1) = 0 ORDER BY pkey1 DESC
The index of this table is (pkey1,pkey2,pkey3) BTREE, and the column count is 20.
The ORDER BY used to be ASC and it was running quite slowly, I then changed it to DESC (since i will change this request for a pagination, if this cannot be optimized), and migrated from 5.7x to 8 to be able to recreate this table with a DESC index. It's now even slower.
The questions are :
- Would changing the DATETIME to a DATE (and thus remove HOUR(0), no longer needed) resolve the problem in a satisfying enough way ? I don't need it anymore, especially if it causes problems.
- If not, what can you advise me to do ?
To go further :
Allegedly because of this table, my whole system (weak: 2Gb RAM, Single core, 20GB SSD) is slowed down, and even a simple database connection is delayed by several seconds. If this table is not the case for the slow down (even though the triggering of this request seems to be the problem), here are the alarming variables i got from PHPmyadmin :
As you can see, things are probably wrong.
Thank you for the read !