Re: Terrible performance of a specific query with lots of connections

[Rick James]

How much RAM do you have?
| innodb_buffer_pool_size | 21474836480 |
That is 21GB, I hope you have at least 24GB of RAM!

Before:

EXPLAIN 
SELECT  dr.referenced_document_id
    FROM  document_references dr
    JOIN  profile_documents pd ON (pd.document_id = dr.document_id)
    WHERE  pd.profile_id = '23890'
      AND  dr.referenced_document_id NOT IN (
                 SELECT  document_id
                   FROM  profile_documents
                   WHERE  profile_id = '23890'):

Turn the NOT IN ( SELECT) into a LEFT JOIN, something like:

EXPLAIN 
SELECT  dr.referenced_document_id
    FROM  document_references dr
    JOIN      profile_documents pd ON (pd.document_id = dr.document_id)
    LEFT JOIN profile_documents pd2
               ON pd2.document_id = dr.referenced_document_id
    WHERE   pd.profile_id = '23890'
      AND  pd2.profile_id = '23890'
      AND  pd2.document_id IS NULL