[FIXED] Why does this update query not complete?

Issue

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.

Answered By - Salman A

Answer Checked By - Willingham (PHPFixing Volunteer)