Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS (
    CASE WHEN products.parent_product_id <> 0 THEN products.parent_product_id ELSE products.product_id END
  ) AS product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  MIN(
    IF(
      prices.percentage_discount = 0, 
      prices.price, 
      prices.price - (
        prices.price * prices.percentage_discount
      )/ 100
    )
  ) as price, 
  GROUP_CONCAT(
    products.product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_ids, 
  GROUP_CONCAT(
    products.product_type 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS product_types, 
  GROUP_CONCAT(
    products.parent_product_id 
    ORDER BY 
      products.parent_product_id ASC, 
      products.product_id ASC
  ) AS parent_product_ids, 
  products.product_type, 
  products.parent_product_id, 
  descr1.full_description as full_description 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'en' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  LEFT JOIN cscart_product_prices as prices_2 ON prices.product_id = prices_2.product_id 
  AND prices_2.lower_limit = 1 
  AND prices_2.price < prices.price 
  AND prices_2.usergroup_id IN (0, 0, 1) 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id 
  INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN cscart_categories ON cscart_categories.category_id = 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_categories.status IN ('A', 'H') 
  AND cscart_categories.storefront_id IN (0, 1) 
  LEFT JOIN cscart_ec_extra_product_data AS epd ON epd.product_id = products.product_id 
WHERE 
  1 
  AND products.product_id NOT IN (130) 
  AND companies.status IN ('A') 
  AND prices.price >= 47.45 
  AND prices.price <= 52.45 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND products.status IN ('A') 
  AND prices.usergroup_id IN (0, 0, 1) 
  AND prices_2.price IS NULL 
  AND epd.product_id IS NULL 
  AND products.product_type != 'D' 
GROUP BY 
  product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 3

Query time 0.00430

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "40.34"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": true,
        "buffer_result": {
          "using_temporary_table": true,
          "nested_loop": [
            {
              "table": {
                "table_name": "companies",
                "access_type": "ALL",
                "possible_keys": [
                  "PRIMARY"
                ],
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 1,
                "filtered": "50.00",
                "cost_info": {
                  "read_cost": "2.29",
                  "eval_cost": "0.20",
                  "prefix_cost": "2.49",
                  "data_read_per_join": "8K"
                },
                "used_columns": [
                  "company_id",
                  "status",
                  "company"
                ],
                "attached_condition": "(`pankajecarter_systemsix`.`companies`.`status` = 'A')"
              }
            },
            {
              "table": {
                "table_name": "cscart_categories",
                "access_type": "ALL",
                "possible_keys": [
                  "PRIMARY",
                  "c_status",
                  "p_category_id"
                ],
                "rows_examined_per_scan": 85,
                "rows_produced_per_join": 3,
                "filtered": "4.00",
                "using_join_buffer": "Block Nested Loop",
                "cost_info": {
                  "read_cost": "19.80",
                  "eval_cost": "0.68",
                  "prefix_cost": "22.97",
                  "data_read_per_join": "11K"
                },
                "used_columns": [
                  "category_id",
                  "storefront_id",
                  "usergroup_ids",
                  "status"
                ],
                "attached_condition": "(((`pankajecarter_systemsix`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`pankajecarter_systemsix`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`pankajecarter_systemsix`.`cscart_categories`.`usergroup_ids`)) and (`pankajecarter_systemsix`.`cscart_categories`.`status` in ('A','H')) and (`pankajecarter_systemsix`.`cscart_categories`.`storefront_id` in (0,1)))"
              }
            },
            {
              "table": {
                "table_name": "products_categories",
                "access_type": "ref",
                "possible_keys": [
                  "PRIMARY",
                  "pt"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "category_id"
                ],
                "key_length": "3",
                "ref": [
                  "pankajecarter_systemsix.cscart_categories.category_id"
                ],
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 6,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "3.54",
                  "eval_cost": "1.36",
                  "prefix_cost": "27.87",
                  "data_read_per_join": "108"
                },
                "used_columns": [
                  "product_id",
                  "category_id"
                ],
                "attached_condition": "(`pankajecarter_systemsix`.`products_categories`.`product_id` <> 130)"
              }
            },
            {
              "table": {
                "table_name": "products",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "status"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "pankajecarter_systemsix.products_categories.product_id"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 0,
                "filtered": "8.82",
                "cost_info": {
                  "read_cost": "6.80",
                  "eval_cost": "0.12",
                  "prefix_cost": "36.03",
                  "data_read_per_join": "3K"
                },
                "used_columns": [
                  "product_id",
                  "product_type",
                  "status",
                  "company_id",
                  "usergroup_ids",
                  "parent_product_id"
                ],
                "attached_condition": "((`pankajecarter_systemsix`.`products`.`company_id` = `pankajecarter_systemsix`.`companies`.`company_id`) and ((`pankajecarter_systemsix`.`products`.`usergroup_ids` = '') or find_in_set(0,`pankajecarter_systemsix`.`products`.`usergroup_ids`) or find_in_set(1,`pankajecarter_systemsix`.`products`.`usergroup_ids`)) and (`pankajecarter_systemsix`.`products`.`status` = 'A') and (`pankajecarter_systemsix`.`products`.`product_type` <> 'D'))"
              }
            },
            {
              "table": {
                "table_name": "prices",
                "access_type": "ref",
                "possible_keys": [
                  "usergroup",
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "key": "usergroup",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "pankajecarter_systemsix.products_categories.product_id"
                ],
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 0,
                "filtered": "3.08",
                "index_condition": "((`pankajecarter_systemsix`.`prices`.`lower_limit` = 1) and (`pankajecarter_systemsix`.`prices`.`usergroup_id` in (0,0,1)))",
                "cost_info": {
                  "read_cost": "1.52",
                  "eval_cost": "0.01",
                  "prefix_cost": "37.79",
                  "data_read_per_join": "0"
                },
                "used_columns": [
                  "product_id",
                  "price",
                  "percentage_discount",
                  "lower_limit",
                  "usergroup_id"
                ],
                "attached_condition": "((`pankajecarter_systemsix`.`prices`.`price` >= 47.45) and (`pankajecarter_systemsix`.`prices`.`price` <= 52.45))"
              }
            },
            {
              "table": {
                "table_name": "prices_2",
                "access_type": "ref",
                "possible_keys": [
                  "usergroup",
                  "product_id",
                  "lower_limit",
                  "usergroup_id"
                ],
                "key": "usergroup",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "3",
                "ref": [
                  "pankajecarter_systemsix.products_categories.product_id"
                ],
                "rows_examined_per_scan": 2,
                "rows_produced_per_join": 0,
                "filtered": "9.25",
                "not_exists": true,
                "cost_info": {
                  "read_cost": "0.09",
                  "eval_cost": "0.00",
                  "prefix_cost": "37.90",
                  "data_read_per_join": "0"
                },
                "used_columns": [
                  "product_id",
                  "price",
                  "lower_limit",
                  "usergroup_id"
                ],
                "attached_condition": "(<if>(found_match(prices_2), isnull(`pankajecarter_systemsix`.`prices_2`.`price`), true) and <if>(is_not_null_compl(prices_2), ((`pankajecarter_systemsix`.`prices_2`.`lower_limit` = 1) and (`pankajecarter_systemsix`.`prices_2`.`price` < `pankajecarter_systemsix`.`prices`.`price`) and (`pankajecarter_systemsix`.`prices_2`.`usergroup_id` in (0,0,1))), true))"
              }
            },
            {
              "table": {
                "table_name": "epd",
                "access_type": "ALL",
                "rows_examined_per_scan": 3,
                "rows_produced_per_join": 0,
                "filtered": "33.33",
                "not_exists": true,
                "using_join_buffer": "Block Nested Loop",
                "cost_info": {
                  "read_cost": "2.42",
                  "eval_cost": "0.00",
                  "prefix_cost": "40.33",
                  "data_read_per_join": "0"
                },
                "used_columns": [
                  "product_id"
                ],
                "attached_condition": "(<if>(found_match(epd), isnull(`pankajecarter_systemsix`.`epd`.`product_id`), true) and <if>(is_not_null_compl(epd), (`pankajecarter_systemsix`.`epd`.`product_id` = `pankajecarter_systemsix`.`products_categories`.`product_id`), true))"
              }
            },
            {
              "table": {
                "table_name": "descr1",
                "access_type": "eq_ref",
                "possible_keys": [
                  "PRIMARY",
                  "product_id"
                ],
                "key": "PRIMARY",
                "used_key_parts": [
                  "product_id",
                  "lang_code"
                ],
                "key_length": "9",
                "ref": [
                  "pankajecarter_systemsix.products_categories.product_id",
                  "const"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 0,
                "filtered": "100.00",
                "cost_info": {
                  "read_cost": "0.01",
                  "eval_cost": "0.00",
                  "prefix_cost": "40.34",
                  "data_read_per_join": "37"
                },
                "used_columns": [
                  "product_id",
                  "lang_code",
                  "product",
                  "full_description"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Result

product_id	product	company_name	price	product_ids	product_types	product_type	full_description
233	Crystal Lake Warm Weather Sleeping Bag	CS-Cart	47.99000000	233	P	P	<p>• Coleman® warm weather sleeping bags made for camping temperatures between 40 and 60 degrees<br />• Rectangle shape, 33” x 75”, fits up to 5’11” tall<br />• 3 pounds of ThermoTech® insulation<br />• Cotton cover with soft cotton liner<br />• Essential camping gear, sleeping bag for a better night outdoors<br />• Don’t forget the Coleman® camping tent to go with your sleeping bags<br />• ComfortSmart™ Technology includes:<br />• ZipPlow™ plows fabric away from zipper to prevent snags<br />• ComfortCuff™ surrounds your face with softness<br />• Roll Control™ locks bag in place for easier rolling<br />• FiberLock™ prevents insulation from shifting, increases durability<br />• ThermoLock™ construction reduces heat loss through the zipper, keeping you warmer<br />• ZipperGlide™ tailoring allows smooth zipper operation around the corners<br />• QuickCord™ for easy, no tie closure<br />• Commercial Machine washable<br />• Five year limited warranty<br />• Made in China</p>
81	Minnetonka Original Cowhide Driving Moc	CS-Cart	49.50000000	81	P	P	<div class="linePadding leadingEquity">Authentic designs. Free-spirited heritage. Shaping the moccasin's place in history since 1946.</div> <div class="linePadding">Leather</div> <ul class="equityBullets firstSet"> <li>• Genuine leather construction </li> <li>• Lace-up front </li> <li>• Long-wearing nub bottom </li> <li>• Cushioned leather insole </li> <li>• Contrast stitching </li> </ul>
36	Routing Protocols and Concepts: CCNA Exploration Companion Guide	CS-Cart	51.00000000	36	P	P	<p> </p> <p style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000; text-align: left; margin: 0px;"><em>Routing Protocols and Concepts</em>, CCNA Exploration Companion Guide is the official supplemental textbook for the Routing Protocols and Concepts course in the Cisco Networking Academy® CCNA® Exploration curriculum version 4. This course describes the architecture, components, and operation of routers, and explains the principles of routing and the primary routing protocols. The Companion Guide, written and edited by Networking Academy instructors, is designed as a portable desk reference to use anytime, anywhere. The book’s features reinforce the material in the course to help you focus on important concepts and organize your study time for exams.</p> <p style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000; text-align: left; margin: 0px;"> </p> <p style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000; text-align: left; margin: 0px;">New and improved features help you study and succeed in this course:</p> <ul style="font-family: Arial, Helvetica, sans-serif; font-size: 12px; color: #000000; text-align: left;"> <li style="font-size: 1em;">Chapter objectives—Review core concepts by answering the focus questions listed at the beginning of each chapter.</li> <li style="font-size: 1em;">Key terms—Refer to the updated lists of networking vocabulary introduced and turn to the highlighted terms in context in each chapter.</li> <li style="font-size: 1em;">Glossary—Consult the comprehensive glossary with more than 150 terms.</li> <li style="font-size: 1em;">Check Your Understanding questions and answer key—Evaluate your readiness with the updated end-of-chapter questions that match the style of questions you see on the online course quizzes. The answer key explains each answer.</li> <li style="font-size: 1em;">Challenge questions and activities—Strive to ace more challenging review questions and activities designed to prepare you for the complex styles of questions you might see on the CCNA exam. The answer key explains each answer. </li> </ul> <p> </p>