CODEWITHSUNDEEP
mysqlalias
djmj
djmj

Reputation: 5544

MySQL query extreme slow filtering on CASE WHEN formula

Using MySQL 5.6 i encounter a big performance problem when filtering on a calculated formula using conditions with CASE WHEN THEN ELSE END syntax.

This SQL formula is mapped by Hibernate. Around 6000 rows in database.
Table foo has indices on columns product and barcode

1. slow 2-16 s

select 
    count(*)
from 
    foo AS f
where 
    (
        CASE WHEN f.product IS NOT NULL THEN 1
        ELSE (
            CASE WHEN f.barcode IS NULL THEN 0
            ELSE  (
                SELECT EXISTS(
                    SELECT 1
                    FROM product AS p
                    WHERE p.barcode = f.barcode
                    LIMIT 1
                )
            ) END
        ) END
    ) = 0

EXPLAIN results:

+----+--------------------+-------+------+-------------------------------+-----+---------+-----+-------+---------------------------------------------------+
| id |    select_type     | table | type |         possible_keys         | key | key_len | ref | rows  |                       Extra                       |
+----+--------------------+-------+------+-------------------------------+-----+---------+-----+-------+---------------------------------------------------+
|  1 | PRIMARY            | f     | ALL  |                               |     |         |     |   700 | Using where                                       |
|  3 | DEPENDENT SUBQUERY | p     | ALL  | UQ_product,IX_product_barcode |     |         |     |  3134 | Range checked for each record (index map: 0x2008) |
+----+--------------------+-------+------+-------------------------------+-----+---------+-----+-------+---------------------------------------------------+

2. fast ~ 0.4 s

select 
    *
from 
    foo AS f
where 
    (CASE ... END) = 0

EXPLAIN results identical to count query.

Upvotes: 1

Views: 1838

Answers (1)

lc.
lc.

Reputation: 116498

First of all you should probably try to look at the output of EXPLAIN to garner more information.

But either way, let's try cleaning up your query a bit and see if we can't make use of a few indexes. The biggest smell is theCASE xxx = 0; I wonder if the query parser is having trouble making an efficient plan and is calculating this value per row and comparing the result to 0.

So let's rewrite this as:

where f.product is null
and (
    f.barcode is null 
    or exists (select 1 from product p where p.barcode = f.barcode)
)

If this does not fix the problem, try updating your index statistics with ANALYZE TABLE.

Upvotes: 2

Related Questions