How Can I find the Slowness of Mysql Query using Explain in my application?

Question

I've developed a Spring Boot application and using MySql to store the value. It quite well when I use the API to store the value but when I used to store the rows using multiple of 1000 (1K, 10K, 100K, etc), it took a long time to complete. How can I find where it lacking time to store the data.

MySql insert queries took a longer time to insert a row. How can I find the slowness of Mysql query?

I have added the following line in logback.xml,

<Logger name="org.hibernate.type.descriptor.sql" level="trace"/>

But it gives the binding parameters twice for the same value, is this an issue?

 select
        schema0_.id as id1_0_,
        schema0_.active as active2_0_,
        schema0_.body as body3_0_,
        schema0_.created_at as created_4_0_,
        schema0_.created_by as created_5_0_,
        schema0_.name as name6_0_,
        schema0_.updated_at as updated_7_0_,
        schema0_.version as version8_0_ 
    from
        my_schema schema0_ 
    where
        schema0_.name=? 
        and schema0_.version=? 
        and schema0_.active=1
binding parameter [1] as [VARCHAR] - [xxxxx]
binding parameter [1] as [VARCHAR] - [xxxxx]
binding parameter [2] as [VARCHAR] - [1.0]
binding parameter [2] as [VARCHAR] - [1.0]
extracted value ([id1_0_] : [BIGINT]) - [1]
extracted value ([id1_0_] : [BIGINT]) - [1]

Is storing the issue or retrieving? When storing large quantities of data through hibernate you will run into issues with performance when not done correctly. — M. Deinum, Mar 16 '21 at 06:23
while storing only taking longer time.. retrieve is no an issue. — SST, Mar 16 '21 at 06:27
Then you aren't flushing and clearing the first lvel cache leading to increasing times for storage due to dirty checking. See https://vladmihalcea.com/the-best-way-to-do-batch-processing-with-jpa-and-hibernate/ and https://vladmihalcea.com/how-to-batch-insert-and-update-statements-with-hibernate/ on how to properly do batch inserts (large datasets) with JPA/Hibernate. — M. Deinum, Mar 16 '21 at 06:30
If you are storing a single value and it takes more and more time your table is probably not indexed (or has too many indexes) or there is something else wrong. Without seeing the code this question is going to be impossible to answer. — M. Deinum, Mar 16 '21 at 08:58
Your prose says "store the rows", implying `INSERT` statements. But the code shows `SELECT`. Which is the concern? — Rick James, Mar 17 '21 at 17:02
Hibernate engine. And I have added an index for missed table, now its quite faster than before. — SST, Mar 18 '21 at 06:49

score 0 · Answer 1 · answered Mar 16 '21 at 06:22

If your my_schema table be very large, and the execution plan MySQL chooses is a full table scan, then this query could easily be time consuming. Also, inserts could simultaneously be fast.

Just telling by the verbose Hibernate output, your query might benefit from the following compound index:

CREATE INDEX idx ON my_schema (name, version, active);

This index, if used, would let MySQL potentially avoid having to check many records when evaluating the query. You may try adding this index directly on MySQL, then testing the performance of Hibernate again.

How Can I find the Slowness of Mysql Query using Explain in my application?

1 Answers1