Slow Query in JdbcJobInstanceDao.getLastJobInstance()

[jickoo]

I use spring-batch-core 4.3.3.
Slow query has occured in FIND_LAST_JOB_INSTANCE_BY_JOB_NAME sql.

public class JdbcJobInstanceDao extends AbstractJdbcBatchMetadataDao implements
JobInstanceDao, InitializingBean {

// ...
	private static final String FIND_LAST_JOB_INSTANCE_BY_JOB_NAME = "SELECT JOB_INSTANCE_ID, JOB_NAME from %PREFIX%JOB_INSTANCE I1 where" +
			" I1.JOB_NAME = ? and I1.JOB_INSTANCE_ID in (SELECT max(I2.JOB_INSTANCE_ID) from %PREFIX%JOB_INSTANCE I2 where I2.JOB_NAME = ?)";

// ...
	@Override
	@Nullable
	public JobInstance getLastJobInstance(String jobName) {
		try {
			return getJdbcTemplate().queryForObject(
					getQuery(FIND_LAST_JOB_INSTANCE_BY_JOB_NAME),
					new Object[] { jobName, jobName },
					new JobInstanceRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
// ...

Executed query.

SELECT
	JOB_INSTANCE_ID,
	JOB_NAME
FROM
	BATCH_JOB_INSTANCE I1
WHERE
	I1.JOB_NAME IN 'myJob'
	AND I1.JOB_INSTANCE_ID IN (
		SELECT
			MAX(I2.JOB_INSTANCE_ID)
		FROM
			BATCH_JOB_INSTANCE I2
		WHERE
			I2.JOB_NAME = 'myJob'
	) 
;

Currently I have about 10,000 rows.
My db socket timeout is 6 seconds and Exception occurred because the above query exceeded 6 seconds.
For MySql query performance, it seems that the query needs to be modified as follows.

solution 1

do not use 'IN' query

SELECT
	JOB_INSTANCE_ID,
	JOB_NAME
FROM
	BATCH_JOB_INSTANCE I1
WHERE
	I1.JOB_NAME = 'myJob'
	AND I1.JOB_INSTANCE_ID = (
		SELECT
			MAX(I2.JOB_INSTANCE_ID)
		FROM
			BATCH_JOB_INSTANCE I2
		WHERE
			I2.JOB_NAME = 'myJob'
	)
; 

solution 2

In my case, I solved it by adding an index.

ALTER TABLE BATCH_JOB_INSTANCE 
ADD INDEX JOB_INST_NAME_ID(JOB_NAME, JOB_INSTANCE_ID);

Thank you.