Native MySQL Query too slow

Hibernate ORM
1

Hi Everyone,

I have a simple table(with name MyEntity) in my MySQL database containing 7 columns.

I have defined a JpaRepo as follows:

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity, String> {
	static final String QUERY = "SELECT * FROM MyEntity WHERE type = ?1 AND (IF(?2 IS NULL, TRUE, (id = ?2)))";

	@Query(value=QUERY, nativeQuery=true)
	public List<MyEntity> searchByTypeAndId(String searchType, String searchId);
}

I am doing performance testing on this app. Each query will result in 10 records getting returned. Each record will weigh around 9KB. So the resultSet of each query will weigh around 90KB. During this testing, searchId will always be null.
At around 360tps(transactions per second), I am observing a huge delay in the query execution.

Related Logs:
“Session Metrics {128580242 nanoseconds spent acquiring 1 JDBC connections; 0 nano seconds spent releasing 0 JDBC connections; 128615605 nanoseconds spent preparing 1 JDBC statements; 18416500 nanoseconds spent executing 1 JDBC statements; 0 nanoseconds spent executing 0 JDBC batches; 0 nanoseconds spent performing 0 L2C puts;0 nanoseconds spent performing 0 L2C hits; 0 nanoseconds spent performing 0 L2C misses; 0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)}”

  1. 128.580242ms for acquiring 1 JDBC connections is pretty huge.
  2. 128.615605ms for preparing 1 JDBC statements is also pretty huge.
  3. 18.416500ms for executing 1 JDBC statements is fine.

Why are points 1 and 2 so high?
What do those values depend on?

Thanks in Advance.

2

Hi, use datagrip ( because this application use jdbc) and try run this query, and look what happend (lock, dont have index and etc) hibernate in

3

Hi,
First of all, thank you for the reply :slight_smile:
Are you implying that the db-design and/or query might not be optimal enough and suggesting me to use datagrip to redesign the db and query?

Yes…my db currently doesn’t have an index. Are you suggesting to add index, locks, etc. in hibernate?

Thank You,
Ashish.

4

Hibernate doesnot have index ! If u add @index into entity ,it doesn’t happend, because it only says when entityfactory up, if u ddl.auto create , into u database create index…

5

Thanks for the reply. I am fully aware that indexes are created over the columns of a table present in the DB. Also, we haven’t enabled ddl.auto-create. My question is, what exactly needs to be done using datagrip tool?

6

if u use postgres u can download pgadmin, if oracle - plsqdevelop, if mysql - another application to connect to database, then add index to table that’s all :wink: u can download datagrip(intelij idea interprise edition) and connect to database and see what happend, u can add index, or particion ,
first of all u need explain plan and see what database do when u select this query .