Currently, in my application with mysql-connector-java:5.1.36, everything works fine. But when I upgrade connector to mysql-connector-java:5.1.47, a query starts to take minutes-2-hours time to execute. If I run the same query directly from the terminal or from the application with v5.1.36, it takes less than a few seconds to execute.
How MySQL connector jar version can affect query performance?
I found the reason. There is a change in mysql-connector-java:5.1.47 and above: When UTF-8 is used for characterEncoding in the connection string, it maps to the MySQL character set name utf8mb4. While for mysql-connector-java:5.1.46 and below it corresponds to utf8 (or utf8mb3 more appropriately).
Link: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-charsets.html
For my database character encoding is set to utf8 (or utf8mb3 more appropriately). Due to this index was not being used in query. After setting server property
it is working fine.