Left join with ISNULL condition query taking long time to execute on MySQL RDS

Question

Kindly help me to optimize below query,this query took more than 60 mints to execute with the ISNULL condition but if we replace ISNULL to <=> condition its took 15 mints to execute but our expectation is this query should take maxmimum 2 mints.

Both table have:- 3880494 records.

SELECT hs.headendid FROM headendlineups_stagging hs LEFT JOIN headendlineups_23march2017 hlp ON hs.headendid=hlp.headendid WHERE ISNULL(hlp.headendid)

Replaced ISNULL with <=> operator

SELECT hs.headendid,hlp.headendid AS productionheadend
FROM headendlineups_stagging hs  
LEFT JOIN  headendlineups_23march2017 hlp ON hs.headendid=hlp.headendid
-- WHERE ISNULL(hlp.headendid)
WHERE hlp.headendid <=> NULL;

Explain plain of both queries

**mysql> EXPLAIN
    ->  SELECT hs.headendid
    ->  FROM headendlineups_stagging hs
    ->  LEFT JOIN headendlineups_23march2017 hlp ON hs.headendid=hlp.headendid
    ->  WHERE ISNULL(hlp.headendid)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hs
   partitions: NULL
         type: index
possible_keys: NULL
          key: IX_lineups_headendid
      key_len: 198
          ref: NULL
         rows: 3854774
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: hlp
   partitions: NULL
         type: ref
possible_keys: IX_lineups_headendid,iDX_linups_NEW,New_headends_linup
          key: IX_lineups_headendid
      key_len: 153
          ref: onconnectdb.hs.HeadendId
         rows: 217
     filtered: 100.00
        Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)**


mysql> EXPLAIN
    -> SELECT hs.headendid,hlp.headendid AS productionheadend
    -> FROM headendlineups_stagging hs
    -> LEFT JOIN  headendlineups_23march2017 hlp ON hs.headendid=hlp.headendid
    -> WHERE hlp.headendid <=> NULL\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hs
   partitions: NULL
         type: index
possible_keys: NULL
          key: IX_lineups_headendid
      key_len: 198
          ref: NULL
         rows: 3854774
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: hlp
   partitions: NULL
         type: ref
possible_keys: IX_lineups_headendid,iDX_linups_NEW,New_headends_linup
          key: IX_lineups_headendid
      key_len: 153
          ref: onconnectdb.hs.HeadendId
         rows: 217
     filtered: 100.00
        Extra: Using where; Using index
2 rows in set, 1 warning (0.00 sec)

Kindly help me to understand if both queries are scanning the same number of records then why the execution time of both queries are different? and kindly suggest the better way of writing above query.

Answer 1

The difference in time is likely to be caching; run each one again.

Change the LEFT JOIN to NOT EXISTS ( SELECT * FROM headendlineups_23march2017 hlp WHERE hs.headendid=hlp.headendid ) (It might or might not help.

Based on the EXPLAINs, it is doing the best it can -- namely Using index on both tables. Face it, you have to scan the entire first table 3.8 million times, then check the second table 3.8 million times.

How much RAM? What is the value of innodb_buffer_pool_size? SHOW TABLE SIZE (for each); I want to compare to the buffer pool size.