Mysql search string in all fields slowness after version upgraded to 5.6

Question

Current mysql version is 5.5. I upgraded a copy to version to 5.6.

Below query get 15secs to execute from 5.5. But the version 5.6 took 750+s to run same query.

Query :

SELECT  SQL_NO_CACHE *
    FROM  RECORDS
    LEFT OUTER JOIN  AUTH  ON RECORDS.`id` = AUTH.`id`
    LEFT OUTER JOIN  STAFFCOMMENTS  ON RECORDS.`id` = STAFFCOMMENTS.`id`
    WHERE  (ODATE LIKE '%Jan%')
      AND  (ODATE LIKE '%2021%')
      AND  RECORDS.NAME <> 'CUSTOMER'
      AND  (RECORDS.NAME <> 'COURIER-ORDER')
  order BY RECORDS.ID DESC
    LIMIT  35000 

Answer 1

Assuming ODATE is of datatype DATE or DATETIME (which it should be!), change

       (ODATE LIKE '%Jan%')
  AND  (ODATE LIKE '%2021%')

to

       ODATE >= '2021-01-01'
  AND  ODATE  < '2021-01-01' + INTERVAL 1 MONTH

Then show us the indexes so we can judge whether they can be improved.