A simple SELECT with ORDER BY on a table of 25 columns and 20 Lakh rows is slow, taking almost 40 seconds.

When ORDER BY is id (PK) the no issue takes around 0.003 seconds but when ORDER BY is on a datetime column with ON UPDATE CURRENT_TIMESTAMP() setting query takes around 40 seconds.

I have tried the following :

  1. SELECT 100 rows based on current_timestamp and then LEFT JOIN with the rest of the column.
  2. Index on current_timestamp but it fail once the post is updated.
  3. innodb_pool_size also tried


ID creat_time access_time title value more columns
  • ID - int(11) AUTO_INCREMENT
  • create_time - datetime
  • access_time - datetime ON UPDATE CURRENT_TIMESTAMP()
  • title - VARCHAR(100)
  • value - LONGTEXT or TEXT
  • more column - some of them are int(6), varchar(512), decimal(3,1) etc


FROM my_table
ORDER BY access_time

also tried

       FROM my_table 
       ORDER BY access_time LIMIT 50
      ) AS PIE 
LEFT JOIN my_table AS A ON (PIE.id = A.id);
  • 4
    Please note when asking performance question always include the execution plan, table description, MySQL version and some data example and expected result because query might be written differently. Nov 21 at 11:43
  • Is there also a LIMIT?
    – Rick James
    Nov 21 at 16:04
  • SELECT * FROM my_table ORDER BY aTime LIMIT 50;
    – Dr.G.Singh
    Nov 22 at 3:49
  • Is there an index on the column used in the order by? Nov 22 at 17:30
  • I am unsure if access_time should be indexed because every time I access a row access_time changes.
    – Dr.G.Singh
    Nov 23 at 4:04


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.