mysql-当我使用子查询时，它需要花费很多时间来执行为什么？

so i want to know why that take so much time.

my query is

SELECT prd.product_id,prd.product_name,
      (prd.product_price+po.additional_price) AS product_price,
       prd.date_added,po.discount,cat.category_name,payment_status,
      (SELECT COUNT(pi.product_impression_id) 
         FROM product_impression pi 
        WHERE pi.product_id = prd.product_id) AS impression_count 
  FROM products prd 
  INNER JOIN product_options po 
  ON po.product_id = prd.product_id 
  AND po.default_option = 'YES' 
  INNER JOIN categories cat 
  ON prd.product_category = cat.category_id 
  WHERE prd.product_artist_id = 103 
  ORDER BY prd.product_id DESC

when i remove following code form query then no issue.

(SELECT COUNT(pi.product_impression_id) 
 FROM product_impression pi 
 WHERE pi.product_id = prd.product_id) AS impression_count

please tell me why?

We can use explain plan in oracle to find the execution plan of a query i.e., the kind of scanning the table does. For eg: full table scan, index unique scan etc.

Try finding the execution plan of the above query using explain.

And check whether the indexes are used or not and also post the output of the explain so that we could analyze further.

thanks