Why will this query not complete?-CodePudding

I have 2 tables, customers (3000 rows) and phone_call_log (350 000 rows).

I need to materialize the time of last call to each customer, using the call log (faster for frontend searches)

Indexes are on : start_time (timestamp), callee(bigint(32) unsigned), caller(bigint(32) unsigned), phonenumber(bigint(32) unsigned), last_call(timestamp)

Running this query without the OR statement completes in < 2 seconds for either caller / callee columns, but with the OR in place, it will not complete (I've not allowed it to run longer than 30 minutes in testing).

UPDATE customers
SET customers.last_call =
        (select max(phone_call_log.start_time)
         from phone_call_log
         WHERE (
                 (
                             phone_call_log.callee = customers.phonenumber
                     )
                 or (
                             phone_call_log.caller = customers.phonenumber
                     )
             )
        )
where customers.phonenumber is not null
  AND LENGTH(customers.phonenumber) > 6
  AND customers.phonenumber > 1000000;

CodePudding user response：

Queries using OR cannot use index (as efficiently). I suggest you try the following:

UPDATE customers
SET last_call = GREATEST(
    (SELECT MAX(start_time) FROM phone_call_log WHERE callee = customers.phonenumber),
    (SELECT MAX(start_time) FROM phone_call_log WHERE caller = customers.phonenumber)
)

Be advised that GREATEST has issues with NULL values.