Why does this query update fail to execute successfully?

Question

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

I need to implement the time of the last call to each customer using call logs (faster for frontend search).

The index is as follows:

• start_time (timestamp)
• call(bigint(32) unsigned)
• Caller(bigint(32) unsigned)
• Phone number (bigint(32) unsigned)
• last_call(timestamp)

When running this query, for the caller/callee columns, the completion time is less than 2 seconds without the OR statement, but with the OR statement, it will not complete (in testing, I did not let it run for more than 30 minutes ).

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;

Answer 1

fastest

When a call comes in, change the data flow to update customers.last_call.

Update connection

UPDATECompared with JOIN, IN (SELECT ...) works better.

or

OR will reduce performance. The query will most likely scan the entire phone_call_log for each customer.

One workaround is to do two UPDATE, and use the appropriate index:

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

This requires creating the following index on phone_call_log:

INDEX(callee, start_time)
    INDEX(caller, start_time)

AndDelete the current single column index caller and callee.

type of data

For phone numbers, using BIGINT may be wrong, especially considering LENGTH(customers.phonenumber) > 6.

Actually, all of this boils down to a simple test:

where customers.phonenumber is not null
  AND LENGTH(customers.phonenumber) > 6
  AND customers.phonenumber > 1000000;

Every > check checks NOT NULL; only one of them is used, depending on the data type, and indexed.

(Please provide SHOW CREATE TABLE; 'English' is not accurate enough.)

Answer 2

Queries using OR cannot effectively use the index. 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)
)

Please note that GREATEST has issues handling NULL values.