How to convert a NOT IN query into a JOIN query

Question

I have this following query that needs to be optimized

Select
    1 As status,
    e.entity_id,
    e.attribute_set_id,
    e.type_id,
    e.created_at,
    e.updated_at,
    e.sku,
    e.name,
    e.short_description,
    e.image,
    e.small_image,
    e.thumbnail,
    e.url_key,
    e.free,
    e.number_of_downloads,
    e.sentence1,
    e.url_path
From
    catalog_product_flat_1 As e
    Inner Join catalog_category_product_index_store1 As cat_index
        On cat_index.product_id = e.entity_id And
            cat_index.store_id = 1 And
            cat_index.visibility In (3, 2, 4) And
            cat_index.category_id = '2'
Where
    e.entity_id Not In (13863, 14096, 13856, 13924, 15875, 15869, 13788, 15977, 15873, 17141, 22214, 16900, 14485,
    15628, 15656, 14220, 14259, 14284, 13875, 13216, 14168, 13892, 16540, 19389, 17286, 16591, 30178, 31517, 31734,
    31621, 2487, 2486, 2485, 2484, 2483, 2482, 2481, 2480, 2479, 2478, 2477, 2475, 2474, 2473, 13402, 13427, 13694,
    13774, 13804, 13837, 13849, 13864, 30299, 30300) And
    e.free = 1
Order By
    e.number_of_downloads Desc;

Here The ids passed in NOT IN() are the "product_id" column values from a table named "mcsdownloads"

So my goal here is to replace NOT IN with a JOIN operation on table "mcsdownloads".

please help !

Answer 1

You are looking for LEFT JOIN ... WHERE ... IS NULL:

SELECT  ...
    From  catalog_product_flat_1 As e
    Inner Join  catalog_category_product_index_store1 As cat_index
            ON cat_index.product_id = e.entity_id
    LEFT JOIN  mcsdownloads AS m  ON m.entity_id = e.entity_id
    WHERE  cat_index.store_id = 1
      And  cat_index.visibility In (3, 2, 4)
      And  cat_index.category_id = '2'
      AND  e.free = 1
      AND  m.entity_id IS NULL 

(Note: I moved the filtering criteria from ON to WHERE. ON is used for saying how tables relate; WHERE is for filtering.)

Indexes needed:

cat_index:  INDEX(store_id, category_id, visibility, product_id)
e:  INDEX(free, number_of_downloads)
m:  INDEX(entity_id)

Answer 2

I don't think there is a way to perform a join instead of not in, but you can arrange your code in the following way so that it works correctly without manually writing down all the product_id from the mcsdownloads table

SELECT 
   1 AS status, e.entity_id, e.attribute_set_id, 
   e.type_id, e.created_at, e.updated_at, e.sku, 
   e.name, e.short_description, e.image, e.small_image, 
   e.thumbnail, e.url_key, e.free, e.number_of_downloads, 
   e.sentence1, e.url_path 
FROM catalog_product_flat_1 AS e 
INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id 
  AND cat_index.store_id=1 
  AND cat_index.visibility IN (3,2,4) 
  AND cat_index.category_id='2' 
WHERE (e.entity_id NOT IN (SELECT product_id FROM mcsdownloads)) 
  AND (e.free = 1) 
ORDER BY e.number_of_downloads DESC;

This is the significant part that I changed

e.entity_id NOT IN (SELECT product_id FROM mcsdownloads)