When a query is executed on database using workbench (sql client) directly, it returns in around 500 ms.
But when its executed from java layer (either through mybatis or spring named template) using mariadb driver, query behaves differently.
First execution takes around 1 second but subsequent execution takes around 45-50 seconds. This is consistently reproducible.
It is observed that when query is waiting for response from jdbc layer, tables involved in query are locked.
When we change jdbc driver from mariadb to mysql driver, then query performance responds consistently in 1-2 seconds for all execution.
For now, we have switched to mysql driver for connecting to AWS Aurara db. AWS recommends to use mariadb driver but we are planning to revert to mysql driver for now to tackle this issue.
SELECT SQL_CALC_FOUND_ROWS group_key, max(tasks.create_time_) max_create_date, count(tasks.id_) tasks_count from ( select distinct task.* from ACT_RU_TASK task left outer join ACT_RU_IDENTITYLINK idlink on task.id_ = idlink.task_id_ and idlink.type_ = 'candidate' where (lower(task.assignee_) = 'user1' or lower(idlink.user_id_) = 'user1') and (task.FOLLOW_UP_DATE_ is null or task.FOLLOW_UP_DATE_ <= '2017-07-25 00:26:02')) tasks, (select PROC_INST_ID_, text_ group_key from ACT_RU_VARIABLE where name_ = 'var1' ) taskVars where tasks.proc_inst_id_ = taskVars.proc_inst_id_ group by group_key order by max(tasks.create_time_) desc limit 0, 0;