Search code examples
mysqlsql-updatequery-optimization

Why does this update query not complete?

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;
Solution

  • 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.