Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  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_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) 
WHERE 
  1 
  AND cscart_categories.category_id IN (222) 
  AND companies.status IN ('A') 
  AND products.company_id = 1 
  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 products.company_id = 1 
  AND products.parent_product_id = 0 
  AND products.company_id IN('1', '2', '3', '4', '5', '6') 
  AND products.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 12

Query time 0.00116

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5.98"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.20",
                "prefix_cost": "0.00",
                "data_read_per_join": "7K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.20",
                "prefix_cost": "0.00",
                "data_read_per_join": "2K"
              },
              "used_columns": [
                "category_id",
                "storefront_id",
                "usergroup_ids",
                "status"
              ]
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 3,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "1.05",
                "eval_cost": "0.60",
                "prefix_cost": "1.65",
                "data_read_per_join": "48"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "status",
                "idx_parent_product_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "atulecarter_atul_demo1.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "8.52",
              "cost_info": {
                "read_cost": "3.00",
                "eval_cost": "0.05",
                "prefix_cost": "5.25",
                "data_read_per_join": "1K"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "usergroup_ids",
                "parent_product_id"
              ],
              "attached_condition": "((`atulecarter_atul_demo1`.`products`.`parent_product_id` = 0) and (`atulecarter_atul_demo1`.`products`.`company_id` = 1) and ((`atulecarter_atul_demo1`.`products`.`usergroup_ids` = '') or find_in_set(0,`atulecarter_atul_demo1`.`products`.`usergroup_ids`) or find_in_set(1,`atulecarter_atul_demo1`.`products`.`usergroup_ids`)) and (`atulecarter_atul_demo1`.`products`.`status` = 'A') and (`atulecarter_atul_demo1`.`products`.`product_type` <> 'D'))"
            }
          },
          {
            "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": [
                "atulecarter_atul_demo1.products_categories.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.26",
                "eval_cost": "0.05",
                "prefix_cost": "5.56",
                "data_read_per_join": "1K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product",
                "full_description"
              ]
            }
          },
          {
            "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": [
                "atulecarter_atul_demo1.products_categories.product_id"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 0,
              "filtered": "97.36",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.27",
                "eval_cost": "0.15",
                "prefix_cost": "5.98",
                "data_read_per_join": "17"
              },
              "used_columns": [
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "attached_condition": "((`atulecarter_atul_demo1`.`prices`.`lower_limit` = 1) and (`atulecarter_atul_demo1`.`prices`.`usergroup_id` in (0,0,1)))"
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_type	full_description
17	101 Things Everyone Should Know About Economics A Down and Dirty Guide to Everything from Securities and Derivatives to Interest Rates and Hedge Funds—And What They Mean For You	CS-Cart	P	<p><span style="color: #000000; font-family: Arial, sans-serif; font-size: 12px; background-color: #ffffff;">What you don't know about economics can hurt you—now more than ever. This easy-to-understand guide answers all the questions you need to know to secure your financial future, such as:</span></p> <ul style="text-align: left; color: #000000; font-size: 12px; background-color: #ffffff;"> <li style="text-align: left;">What does it mean to my paycheck when the Fed lowers or raises interest rates?</li> <li style="text-align: left;">What's the difference between bonds, securities, and derivatives—and which should I invest in now?</li> <li style="text-align: left;">What does Keynesian economics have to do with my savings?</li> </ul> <p><span style="color: #000000; font-family: Arial, sans-serif; font-size: 12px; background-color: #ffffff;">For those people whose heads spin when reading the business pages of the newspaper, here's a roadmap through the economic jungle. In simple, plain language, Peter Sander explains how economies work, why they grow, how they contract, and what the government can and can't do to help them. Most important, he tells you how all this affects </span><em style="color: #000000; font-family: Arial, sans-serif; font-size: 12px; background-color: #ffffff;">you</em><span style="color: #000000; font-family: Arial, sans-serif; font-size: 12px; background-color: #ffffff;">—and what kind of changes you're going to see in your finances as a result.</span></p> <p style="text-align: left; color: #000000; font-size: 12px; background-color: #ffffff;">Economics has been called the "dismal" science. But it doesn't need to be gloomy or impenetrable. This book is an essential guide for anyone who wants to understand where the economy is today, where it's going, and what it means for the rest of us.</p> <p> </p>
20	The ABCs of Gold Investing. How to Protect and Build Your Wealth with Gold	CS-Cart	P	<p><em style="color: #000000; font-family: Verdana; background-color: #ffffff; font-size: small;"><strong>The ABCs of Gold Investing</strong></em><span style="color: #000000; font-family: Verdana; background-color: #ffffff; font-size: small;"> (Second Edition) is a solid resource for investors wishing to understand the benefits of private gold ownership. In this reader-friendly book, author Michael J. Kosares addresses the rapid changes in both the American and world economies and explains why gold is important to you as an investor. And, with thirty years experience in the gold business, he can tell you how to add gold to your portfolio in a safe and economical manner.</span></p>
13	Your Idea, Inc. 12 Steps to Building a Million Dollar Business -- Starting Today!	CS-Cart	P	<p style="text-align: left; color: #000000; font-size: 12px; background-color: #ffffff;"> Every time a new story about how some nobody from nowhere got rich producing some clever new product in his garage, you may think, "Why can't I do that?" Well, anyone can--the trick is to take those good ideas and build them into great products that can succeed in the marketplace. In this book, you will get the 12-step plan you need to make your new product or service a profitable reality. You will learn important skills for success, including how to: </p> <ul style="text-align: left; color: #000000; font-size: 12px; background-color: #ffffff;"> <li style="text-align: left;">Refine their idea to attract a target audience</li> <li style="text-align: left;">Research the competition</li> <li style="text-align: left;">Find the right manufacturer</li> <li style="text-align: left;">Create appropriate brand messaging</li> <li style="text-align: left;">Build buzz online and beyond</li> <li style="text-align: left;">Work trade shows and conventions</li> </ul> <p> <span style="color: #000000; font-family: Arial, sans-serif; font-size: 12px; background-color: #ffffff;">Written by a woman with no formal business experience who turned her own idea into a million-dollar company, this book is the pragmatic yet inspiring guide every aspiring entrepreneur is looking for.</span> </p>