MySQL queries are very slow even using INNER JOIN instead of IN

Question

I have three tables：product (10k records), product_attribute (4k records), and product_value (2m records). product and product_attribute are connected via product_value. I want to retrieve a specific product (e.g., product_id = 001) and its respective attribute name and attribute value. At first, I try

SELECT product.product_id, product_attribute.attribute_name, product_value.attribute_value

FROM product, product_attribute, product_value

WHERE product.product_id = 001 AND product.product_id = product_value.product_id AND product_attribute.product_attribute_id IN (SELECT product_value.product_attribute_id FROM product_value WHERE product_value.product_id = 001)

But it is extremely slow. Then I used INNER JOIN instead of IN

SELECT product.product_id, product_attribute.attribute_name,  product_value.attribute_value FROM product

INNER JOIN product_value ON product.product_id = 001 AND product.product_id = product_value.product_id

INNER JOIN product_attribute ON product_attribute.product_attribute_id = product_value.product_attribute_id WHERE product.product_id = 001

But it is still very slow: the query returns 31 rows in 36 minutes!

Is there any better solution for this problem?

Explaining the query gives:

*************************** 1. row ***********
           id: 1
  select_type: SIMPLE
        table: product_attribute
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5247
        Extra:
*************************** 2. row ***********
           id: 1
  select_type: SIMPLE
        table: product
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 91818
        Extra: Using where; Using join buffer
*************************** 3. row ***********
           id: 1
  select_type: SIMPLE
        table: product_value
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1731016
        Extra: Using where; Using join buffer

Answer 1

Try this:

SELECT p.product_id, pa.attribute_name, pv.attribute_value 
FROM product p 
INNER JOIN product_value pv ON p.product_id = pv.product_id
INNER JOIN product_attribute pa ON pa.product_attribute_id = pv.product_attribute_id 
WHERE p.product_id = 001

Run this query and if you still facing the query is slow than add you EXPLAIN plan of above query

You have to create a INDEX on columns to improve performance.

1. Create index on product_id column of product_value table

2. Create index on product_attribute_id column of product_value table