MySQL poor OR and ISNULL performance

Question

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 same 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 should 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 1

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;

Answer 2

1. Use a multi-table UPDATE instead of = ( SELECT ...)

2. Instead of OR, write two separate UPDATEs.