CODEWITHSUNDEEP
mysqlsql
Aaron Kelly
Aaron Kelly

Reputation: 33

A Simple Join and In Clause taking too long

I am attempting to get results from the combined table using the SQL below for the inventory status information. But the problem came when I am using about 36,000 variant IDs to the IN clause parameter and got a timeout on the website due to protection against timeout activity. So most likely the query took longer than 3 min max.

SELECT vid.`variant_id`, name, iq.`quantity` 
FROM products vid 
JOIN inventoryQuantity iq USING(inventory_item_id) 
JOIN vendor v ON iq.`location_id` = v.`shopify_location_id` 
WHERE vid.`variant_id` IN (123456,98764,..)

Here are three tables:

products
id | variant_id | sku | inventory_item_id
-----------------------------------------
1         123456 12-S            40005212
2         123457 13-M            40058213

inventoryQuantity
id | inventory_item_id | location_id | quantity
-----------------------------------------------
1              40005212         0001        50
2              40058213.        0004         5

vendor
company_id | name | shopify_location_id | logicbroker_id | mage_vendor_id | enable
----------------------------------------------------------------------------------
1000         Kroll                 0001            12574     MagVendID_12       1
1001         Luxo.                 0004            12574     MagVendID_13       0

And here is EXPLAIN SELECT result using about 500 variant IDs: screenshot

id|select_type|table|partitions|type|possible_keys|key|key-len| ref |  rows  |filtered| Extra
1   SIMPLE         v    NULL     ALL    NULL       NULL NULL    NULL    9       100.00  NULL
1   SIMPLE         iq   NULL     ALL    NULL       NULL NULL    NULL    49907   10.00   Using where; Using join buffer (Block Nested Loop)
1   SIMPLE         vid  NULL     ALL    NULL       NULL NULL    NULL    34422   5.00    Using where; Using join buffer (Block Nested Loop)

So, how do I optimize this query to be faster in less than 3 min (around 1 sec or less is better)?

Upvotes: 3

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You seem to have no keys in your tables. You probably want primary keys, but you can just create indexes:

create index idx_product_variant on product(variant_id);
create index idx_inventoryQuantity_3 on inventoryQuantity(inventory_item_id, location_id, quantity);
create index idx_vendor_2 on vendor(shopify_location_id, name);

Upvotes: 1

Related Questions