title: Why does this query not complete in a reasonable amount of time?
body: <p>The following query including subquery on MySQL 8.0.15 will not complete after 15 minutes. </p>
  
  <p>This query is trying to find six-digit values in the "del" table that are not in the IMA table. The "Message" field in the REGEX_SUBSTR has other text values so this just extracts the six-digit portion subject to the WHERE clause.</p>
  
  <pre><code>SELECT
      ID 
  FROM
      del
  WHERE
      ID NOT IN (
      SELECT DISTINCT
          REGEXP_SUBSTR ( Message, '\\d{6}' ) AS ID1 
      FROM
          IMA 
      WHERE
          AND Message LIKE '%deleted%'
      )
  </code></pre>
  
  <p>Each of the queries individually complete in less than 20 seconds, but together there's a huge problem.</p>
  
  <p>Table sizes are less than 30,000 records for each of the two results.</p>
  
  <p>I tried creating an index on the Message field.</p>
  
  <p>The computer has 128 GB RAM and an i9 processor.</p>
  
  <p>Thanks in advance,</p>