How to convert a NOT IN query into a JOIN query-CodePudding

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 !

CodePudding user response：

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)