1

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 :

enter image description here

As you can see, things are probably wrong.

Thank you for the read !

1

Your query is not sargeable. Using Hour() function on the first column (pk1) of the composite primary key is inhibiting the usage of the Primary Key for index lookup; and thus (in absence of any other proper index), it is most likely doing full table scan. The general rule of thumb to follow while defining a working index is:

  1. First priority should be given to all the columns, which are inside the WHERE clause, and are connected by AND clause, and are compared to a constant value using either =, IS NULL, or <=>. So, in your query, there are two columns following this: Data_Type and Status.

  2. Second priority can be given to following three scenarios:

    • Column having Range condition.
    • Columns in a specific order in the GROUP BY clause (if existing).
    • Columns in a specific order in the ORDER BY clause (if existing)

Now, coming to your questions:

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.

Yes, you should do that; if the time part is irrelevant to you. In MySQL 8+, the size of datetime is 5 bytes, while the size of date is 3 bytes. So you are effectively going to save considerable amount of space as well.

With this change, your new query would simply be:

SELECT * FROM table 
WHERE pkey2 = ... 
  AND pkey3= ... 
  -- removed AND HOUR(pk1) = 0 
ORDER BY pkey1 DESC

In this scenario, if you refer the thumbrule discussed above, you need to define a new Composite Index (pkey2, pkey3, pkey1) (the ordering is changed; you will need to keep this index besides the already defined Primary Key).

ALTER TABLE table_name ADD INDEX (pkey2, pkey3, pkey1);

This should be able to help you considerably.

  • But simply adding this second index, and not changing the datetime field to a date might also help a lot. – Luuk Sep 24 at 18:06
  • @Luuk true; but I think as per OP (although without looking at his real data, it is just a guess), if he changes from Datetime to Date, his query also changes. – Madhur Bhaiya Sep 24 at 18:07
  • 1
    Applied this solution : working perfectly ! Queries now take under 1 second when they used to take several minutes. – Adri Sep 30 at 0:07
0
  1. Create calculated field

    HOURpkey1 TINYINT AS (HOUR(pkey1)) 
    

Make it either STORED or VIRTUAL - think by yourself what is better for you.

  1. Create index

    KEY idx_pkey2_pkey3_HOURpkey1 ON table (pkey2,pkey3,HOURpkey1)
    

    Any fields order is possible and safe for the query in question, think by yourself what is the best.

If the amount of records in output is huge and their (final) sorting is expensive, then add pkey1 into the index expression as the last, 4th, field.


OP will also need to change the query; instead of using HOUR(pkey1) = 0 to HOURpkey1 = 0 (refer the new generated column now)

That's wrong.

See fiddle - idx2 is used in both cases.

  • Thank you for the reply, I have read on some documentation while doing research, that calculated fields aren't indexed ? – Adri Sep 24 at 18:28
  • 1
    OP will also need to change the query; instead of using HOUR(pkey1) = 0 to HOURpkey1 = 0 (refer the new generated column now) – Madhur Bhaiya Sep 24 at 18:28
  • @Adri you can Index Calculated/Generated columns in InnoDB. Please read: dev.mysql.com/doc/refman/8.0/en/… – Madhur Bhaiya Sep 24 at 18:31
  • Alright, thank you for the clarification ! I am currently looking at @MadhurBhaiya's answer first, but this idea seems promising as well. The detail i forgot myself is that the datetime field is actually a timestamp and I relied on the default value as timestamp. But I will figure this out after I remove my duplicates ... – Adri Sep 24 at 18:35
  • @MadhurBhaiya That's wrong. See fiddle - idx2 is used in both cases. You forget that all expression are formalized. – Akina Sep 25 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.