SELECT 
  cscart_products.*, 
  cscart_product_descriptions.*, 
  MIN(
    IF(
      cscart_product_prices.percentage_discount = 0, 
      cscart_product_prices.price, 
      cscart_product_prices.price - (
        cscart_product_prices.price * cscart_product_prices.percentage_discount
      )/ 100
    )
  ) as price, 
  GROUP_CONCAT(
    CASE WHEN (
      cscart_products_categories.link_type = 'M'
    ) THEN CONCAT(
      cscart_products_categories.category_id, 
      'M'
    ) ELSE cscart_products_categories.category_id END 
    ORDER BY 
      (
        cscart_products_categories.link_type = 'M'
      ) DESC, 
      cscart_products_categories.category_position ASC, 
      cscart_products_categories.category_id ASC
  ) as category_ids, 
  popularity.total as popularity, 
  companies.company as company_name, 
  cscart_product_sales.amount as sales_amount, 
  cscart_product_review_prepared_data.average_rating average_rating, 
  cscart_product_review_prepared_data.reviews_count product_reviews_count, 
  cscart_discussion.type as discussion_type 
FROM 
  cscart_products 
  LEFT JOIN cscart_product_prices ON cscart_product_prices.product_id = cscart_products.product_id 
  AND cscart_product_prices.lower_limit = 1 
  AND cscart_product_prices.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id 
  AND cscart_product_descriptions.lang_code = 'ja' 
  LEFT JOIN cscart_companies as companies ON companies.company_id = cscart_products.company_id 
  INNER JOIN cscart_products_categories ON cscart_products_categories.product_id = cscart_products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND (
    cscart_products.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_products.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_products.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A', 'H') 
  AND cscart_products.status IN ('A', 'H') 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = cscart_products.product_id 
  LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = cscart_products.product_id 
  AND cscart_product_sales.category_id = 232 
  LEFT JOIN cscart_product_review_prepared_data ON cscart_product_review_prepared_data.product_id = cscart_products.product_id 
  AND cscart_product_review_prepared_data.storefront_id = 0 
  LEFT JOIN cscart_discussion ON cscart_discussion.object_id = cscart_products.product_id 
  AND cscart_discussion.object_type = 'P' 
WHERE 
  cscart_products.product_id = 195 
  AND (
    companies.status IN ('A') 
    OR cscart_products.company_id = 0
  ) 
GROUP BY 
  cscart_products.product_id

Query time 0.00147

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_products",
      "access_type": "const",
      "possible_keys": ["PRIMARY", "status"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "popularity",
      "access_type": "const",
      "possible_keys": ["PRIMARY", "total"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_product_sales",
      "access_type": "const",
      "possible_keys": ["PRIMARY", "pa"],
      "key": "PRIMARY",
      "key_length": "6",
      "used_key_parts": ["category_id", "product_id"],
      "ref": ["const", "const"],
      "rows": 0,
      "filtered": 0,
      "unique_row_not_found": true
    },
    "table": {
      "table_name": "cscart_product_review_prepared_data",
      "access_type": "const",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "7",
      "used_key_parts": ["product_id", "storefront_id"],
      "ref": ["const", "const"],
      "rows": 0,
      "filtered": 0,
      "unique_row_not_found": true
    },
    "table": {
      "table_name": "cscart_discussion",
      "access_type": "const",
      "possible_keys": ["object_id"],
      "key": "object_id",
      "key_length": "6",
      "used_key_parts": ["object_id", "object_type"],
      "ref": ["const", "const"],
      "rows": 0,
      "filtered": 0,
      "unique_row_not_found": true
    },
    "table": {
      "table_name": "cscart_product_prices",
      "access_type": "ref",
      "possible_keys": ["usergroup", "product_id", "lower_limit", "usergroup_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 1.537283778,
      "attached_condition": "trigcond(cscart_product_prices.lower_limit = 1 and cscart_product_prices.usergroup_id in (0,0,1))"
    },
    "table": {
      "table_name": "cscart_product_descriptions",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "product_id"],
      "key": "product_id",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_product_descriptions.lang_code = 'ja')"
    },
    "table": {
      "table_name": "companies",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["company_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(companies.`status` = 'A')"
    },
    "table": {
      "table_name": "cscart_products_categories",
      "access_type": "ref",
      "possible_keys": ["PRIMARY", "pt"],
      "key": "pt",
      "key_length": "3",
      "used_key_parts": ["product_id"],
      "ref": ["const"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_categories",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
      "key": "PRIMARY",
      "key_length": "3",
      "used_key_parts": ["category_id"],
      "ref": ["smscrm_edev.cscart_products_categories.category_id"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "(cscart_categories.usergroup_ids = '' or find_in_set(0,cscart_categories.usergroup_ids) or find_in_set(1,cscart_categories.usergroup_ids)) and cscart_categories.`status` in ('A','H')"
    }
  }
}

Result

product_id product_code product_type status company_id list_price amount weight length width height shipping_freight low_avail_limit timestamp updated_timestamp usergroup_ids is_edp edp_shipping unlimited_download tracking free_shipping zero_price_action is_pbp is_op is_oper is_returnable return_period avail_since out_of_stock_actions localization min_qty max_qty qty_step list_qty_count tax_ids age_verification age_limit options_type exceptions_type details_layout shipping_params parent_product_id addon_ship_start_date lang_code product shortname short_description full_description meta_keywords meta_description search_words page_title age_warning_message promo_text price category_ids popularity company_name sales_amount average_rating product_reviews_count discussion_type
195 I0195ZFDB8 P A 1 0.00 15 0.000 0 0 0 0.00 0 1328731200 1328765616 0 N N N N Y N N Y 10 0 N N 0 default a:5:{s:16:"min_items_in_box";i:0;s:16:"max_items_in_box";i:0;s:10:"box_length";i:0;s:9:"box_width";i:0;s:10:"box_height";i:0;} 0 0 ja クロスロード・ギター・フェスティヴァル 2010(DVD通常盤) <p> 内容(「キネマ旬報社」データベースより): <br />2010年6月にシカゴで行われた「クロスロード・ギター・フェスティヴァル」の模様を映像化。<br /> エリック・クラプトンを中心に、ジェフ・ベック、スティーヴ・ウィンウッドらギターの名手たちの11時間にも及ぶステージからハイライトシーンを収録。2枚組。</p> <p> DVD 1:</p> <p> 01 Promise Land - By Sonny Landreth with Eric Clapton</p> <p> 02 Z. Rider - By Sonny Landreth with Eric Clapton</p> <p> 03 Traveling Shoes - By Robert Randolph &amp; The Family Band</p> <p> 04 Going Down - By Pino Daniele, Joe Bonamassa, Robert Randolph &amp; The Family Band</p> <p> 05 Killing Floor - By Robert Cray with Jimmie Vaughan &amp; Hubert Sumlin</p> <p> 06 Six Strings Down - By Jimmie Vaughan with Robert Cray &amp; Hubert Sumlin</p> <p> 07 Waiting For The Bus - By ZZ Top</p> <p> 08 Jesus Just Left Chicago - By ZZ Top</p> <p> 09 Gypsy Blood - By Doyle Bramhall II</p> <p> 10 In My Time Of Dying (Jesus Make Up My Dying Bed) - By Doyle Bramhall II</p> <p> 11 Bright Lights - By Gary Clark Jr.</p> <p> 12 Long Road Home - By Sheryl Crow with Derek Trucks, Susan Tedeschi, Doyle Bramhall II &amp; Gary Clark Jr.</p> <p> 13 Our Love Is Fading - By Sheryl Crow with Eric Clapton, Doyle Bramhall II &amp; Gary Clark Jr.</p> <p> 14 Blackwaterside - By Bert Jansch</p> <p> 15 Mississippi Blues - By Stefan Grossman &amp; Keb&#39; Mo&#39;</p> <p> 16 Roll And Tumble Blues - By Stefan Grossman</p> <p> 17 One More Last Chance - By Vince Gill with Keb&#39; Mo&#39;, James Burton, Earl Klugh &amp; Albert Lee</p> <p> 18 Mystery Train - By Vince Gill with James Burton, Albert Lee, Keb&#39; Mo&#39; &amp; Earl Klugh</p> <p> 19 Lay Down Sally - By Vince Gill with Sheryl Crow, Keb&#39; Mo&#39;, Albert Lee, James Burton &amp; Earl Klugh</p> <p> 20 Angelina - By Earl Klugh</p> <p> 21 Vonetta - By Earl Klugh</p> <p> 22 Who Did You Think I Was - By John Mayer Trio</p> <p> 23 Ain&#39;t No Sunshine - By John Mayer Trio</p> <p> &nbsp;</p> <p> DVD 2:</p> <p> 01 Midnight In Harlem - By Derek Trucks &amp; Susan Tedeschi Band</p> <p> 02 Coming Home - By Derek Trucks &amp; Susan Tedeschi Band with Warren Haynes</p> <p> 03 Soulshine - By Warren Haynes</p> <p> 04 Don&#39;t Keep Me Wondering - By David Hidalgo &amp; Cesar Rosas with Derek Trucks</p> <p> 05 Space Captain - By Derek Trucks &amp; Susan Tedeschi Band with Warren Haynes, David Hidalgo, Cesar Rosas &amp; Chris Stainton</p> <p> 06 Five Long Years - By Buddy Guy with Jonny Lang &amp; Ronnie Wood</p> <p> 07 Miss You - By Buddy Guy with Jonny Lang &amp; Ronnie Wood</p> <p> 08 Hammerhead - By Jeff Beck</p> <p> 09 Nessun Dorma - By Jeff Beck</p> <p> 10 Crossroads</p> <p> 11 Hands Of The Saints - By Citizen Cope &amp; Eric Clapton</p> <p> 12 I Shot The Sheriff</p> <p> 13 Shake Your Money Maker - By Eric Clapton &amp; Jeff Beck</p> <p> 14 Had To Cry Today - By Steve Winwood &amp; Eric Clapton</p> <p> 15 Voodoo Chile - By Eric Clapton &amp; Steve Winwood</p> <p> 16 Dear Mr. Fantasy - By Steve Winwood &amp; Eric Clapton</p> <p> 17 The Thrill Is Gone - By B.B. King &amp; Ensemble</p> <div> &nbsp;</div> <p> &nbsp;</p> <p class="product-promo-text"><span class="product-promo-header">5000円以上のお買い上げで送料無料!</span><span class="product-promo-body">本日より2日以内のご注文は来週月曜日の発送となります</span></p> 3600.000000 232M 3 CS-Cart.jp