Arivan Bastos Arivan Bastos - 2 months ago 11
MySQL Question

MySQL poor OR and ISNULL performance

I'm really surprised with some strange mysql performance behaviours. My following query is taking about 3 hours to run:

UPDATE ips_invoice AS f SET ips_locality_id = (
SELECT ips_locality_id
FROM ips_user_unit_locality AS uul
JOIN ips_user AS u ON u.id = uul.ips_user_id
WHERE
(u.id = f.ips_user_id OR u.ips_user_id_holder = f.ips_user_id) AND
uul.date <= f.date

ORDER BY `date` DESC
LIMIT 1
)
WHERE f.ips_locality_id IS NULL;


I also tried the following one, but get some performance results:

UPDATE ips_invoice AS f SET ips_locality_id = (
SELECT ips_locality_id
FROM ips_user_unit_locality AS uul
JOIN ips_user AS u ON u.id = uul.ips_user_id
WHERE
IFNULL(u.ips_user_id_holder, u.id) = f.ips_user_id
AND
uul.date <= f.date

ORDER BY `date` DESC
LIMIT 1
)
WHERE f.ips_locality_id IS NULL;


The logic is: if the "ips_user_id_holder" column is not null, I should use it, if not i sould use "id" column.

If I split the query into two queries, each one take 15 seconds to run:

UPDATE ips_invoice AS f SET ips_locality_id = (
SELECT ips_locality_id
FROM ips_user_unit_locality AS uul
JOIN ips_user AS u ON u.id = uul.ips_user_id
WHERE
u.ips_user_id_holder = f.ips_user_id
AND
uul.date <= f.date

ORDER BY `date` DESC
LIMIT 1
)
WHERE f.ips_locality_id IS NULL;

UPDATE ips_invoice AS f SET ips_locality_id = (
SELECT ips_locality_id
FROM ips_user_unit_locality AS uul
JOIN ips_user AS u ON u.id = uul.ips_user_id
WHERE
u.id = f.ips_user_id
AND
uul.date <= f.date

ORDER BY `date` DESC
LIMIT 1
)
WHERE f.ips_locality_id IS NULL;


It is not the first time I got in issues with Mysql "OR" or "null checks" in
relatively simple queries (Why this mysql query (with is null check) is so slower than this other one?).

The ips_invoice table has about 400.000 records, the ips_user_unit_locality about 100.000 records and ips_user about 35.000 records.

I'm running MySQL 5.5.49 in an Ubuntu Amazon EC2 instance.

So, what is wrong with the first and second queries? What is the cause of significant performance difference?

Answer

There is nothing "wrong" with the first and second queries. However, when you use or in a join condition (or equivalently, a correlated subquery condition), then the engine usually cannot use indexes.

That makes everything really slow.

You seem to understand at least one way to fix it, so I won't propose anything else.

EDIT:

I will note that your query doesn't do exactly what you specify in the text. It gets the latest date for either of the two user ids. You seem to want to prioritize the ids. If so, this is more the query you want:

UPDATE ips_invoice f
    SET ips_locality_id =
        COALESCE( (SELECT ips_locality_id 
                   FROM ips_user_unit_locality uul JOIN
                        ips_user u
                        ON u.id = uul.ips_user_id 
                   WHERE u.ips_user_id_holder, f.ips_user_id AND
                         uul.date <= f.date 
                   ORDER BY uul.date DESC
                   LIMIT 1
                  ),
                  (SELECT ips_locality_id 
                   FROM ips_user_unit_locality uul
                   WHERE uul.ips_user_id = f.ips_user_id AND
                         uul.date <= f.date 
                   ORDER BY uul.date DESC
                   LIMIT 1
                  )
                )
WHERE f.ips_locality_id IS NULL;