[FIXED] Query taking too much time on a large database table with 3 million entries,how to optimize the performance

Issue

Spring Boot Query

@Query(value="SELECT  * 
              FROM products p 
                join product_generic_name pg on pg.id = p.product_generic_name_id 
              where (p.product_name like %?1% 
                    and p.parent_product_id IS NULL 
                    and p.is_active=true and 
                    (p.is_laboratory is null or p.is_laboratory = false)
                    ) 
              or (pg.product_generic_name like %?1% 
                    and pg.is_active = true) ",nativeQuery = true)

Page<Products> findByProductNameLikeAndGenericNameLike(String searchText, Pageable pageable);

The product table has over 3 million entries and query takes around 4 min to complete.How to optimize the query performance. I tried indexing product_name column but not much performance improvement.

Solution

There are two bottlenecks:

• like %?1% -- The leading wildcard means that it must read and check every row.

• OR -- This is rarely optimizable.

If like %?1% is only looking at "words", then using a FULLTEXT index and MATCH will run much faster.

OR can be turned into a UNION. It should probably be UNION DISTINCT, assuming that ?1 could be in both the name and the generic_name.

More memory, more regular indexes, etc, etc -- These are not likely to will help. EXPLAIN and other analysis tools tell you what is going on now, not how to improve the query and/or indexes. Defragmentation (in InnoDB) is mostly a waste of time. There is only a narrow range of CPU speeds; this has not changed in over 20 years. Extra cores are useless since MySQL will use only one core for this query. A mere 3M rows means that you probably have more than enough RAM.

Answered By - Rick James