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 (
    175, 176, 185, 186, 187, 188, 189, 202
  ) 
  AND companies.status IN ('A') 
  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.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 
  12, 12

Query time 0.00189

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "21.58"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "range",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 8,
              "rows_produced_per_join": 0,
              "filtered": "4.00",
              "index_condition": "(`atulecarter_atul_demo1`.`cscart_categories`.`category_id` in (175,176,185,186,187,188,189,202))",
              "cost_info": {
                "read_cost": "19.15",
                "eval_cost": "0.06",
                "prefix_cost": "19.21",
                "data_read_per_join": "855"
              },
              "used_columns": [
                "category_id",
                "storefront_id",
                "usergroup_ids",
                "status"
              ],
              "attached_condition": "(((`atulecarter_atul_demo1`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`atulecarter_atul_demo1`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`atulecarter_atul_demo1`.`cscart_categories`.`usergroup_ids`)) and (`atulecarter_atul_demo1`.`cscart_categories`.`status` in ('A','H')) and (`atulecarter_atul_demo1`.`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": [
                "atulecarter_atul_demo1.cscart_categories.category_id"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.34",
                "eval_cost": "0.19",
                "prefix_cost": "19.74",
                "data_read_per_join": "15"
              },
              "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": "42.58",
              "cost_info": {
                "read_cost": "0.96",
                "eval_cost": "0.08",
                "prefix_cost": "20.89",
                "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`.`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`.`company_id` in ('1','2','3','4','5','6')) and (`atulecarter_atul_demo1`.`products`.`product_type` <> 'D'))"
            }
          },
          {
            "table": {
              "table_name": "companies",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "ref": [
                "atulecarter_atul_demo1.products.company_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "16.67",
              "cost_info": {
                "read_cost": "0.41",
                "eval_cost": "0.01",
                "prefix_cost": "21.38",
                "data_read_per_join": "490"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ],
              "attached_condition": "(`atulecarter_atul_demo1`.`companies`.`status` = 'A')"
            }
          },
          {
            "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.07",
                "eval_cost": "0.01",
                "prefix_cost": "21.46",
                "data_read_per_join": "318"
              },
              "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.07",
                "eval_cost": "0.04",
                "prefix_cost": "21.58",
                "data_read_per_join": "4"
              },
              "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
52	KFC-W3013PS	CS-Cart	P	<p>1200W Peak Power<br /> 400W RMS Power<br /> 4 ohms Impedance<br /> 34Hz - 300Hz Frequency Response<br /> 6" Mounting Depth<br /> PP Cone with Diamond Array Pattern<br /> Dual-Ventilation System<br /> Rubber Surround</p>
33	nüvi® 2455LT	CS-Cart	P	<p>Whether you’re driving through an unfamiliar city or on your daily commute, nüvi 2455LT makes your trip easier than ever before. It includes FREE lifetime traffic updates, advanced lane guidance and more.</p>
32	nüvi® 3450	CS-Cart	P	<p>Garmin Guidance 3.0 allows you to quickly look up addresses and services and be guided to your destination with voice-prompted, turn-by-turn directions that speak street names. It comes preloaded with maps that feature 3-D landmarks and terrain for North America. It also comes preloaded with more than 8 million points of interest and offers the ability to add your own.</p>
55	PRS-D800	CS-Cart	P	<p>The PRS-D800 2-channel amplifier contains audiophile grade components that were carefully selected and design perfected to achieve highest audio quality.</p>
29	TomTom GO LIVE Top Gear edition	CS-Cart	P	<p>The TomTom GO LIVE Top Gear edition satnav! Featuring the voice of Jeremy Clarkson, plus a heap of Top Gear bonus goodies, this special edition satnav is the ultimate traffic- vanquishing weapon for the discerning driverist. With speed camera warnings, information via Local Search with Google™, weather reports and more, it’ll spirit you onto gloriously empty roads when all about you are stuck in jams. With the calming presence of Jeremy Clarkson himself by your side, what could possibly go wrong?</p>
50	TS-D1720C	CS-Cart	P	<p>Absolute fidelity to musical sources takes form from speakers that reproduce the ambience in which sounds originate. Stage size, musicians’ movement, aural reflection and other distinctive details all bring these sounds to life. Pioneer D-Series speakers are available in 2-way component packages or 2-way coaxial designs in multiple sizes that fit most vehicles.</p>
51	TS-D6902R	CS-Cart	P	<p>Absolute fidelity to musical sources takes form from speakers that reproduce the ambience in which sounds originate. Stage size, musicians’ movement, aural reflection and other distinctive details all bring these sounds to life. Pioneer D-Series speakers are available in 2-way component packages or 2-way coaxial designs in multiple sizes that fit most vehicles.</p>
49	TS-MR1640	CS-Cart	P	<p>Whether you're out on the water or at the dock, this 6-1/2” two-way speaker adds a new dimension to your boat. It's built specifically for marine use, with heavy-duty construction inside and out that strenuously resists water, corrosion, heat, and sunlight.</p>
48	TS-MR2040	CS-Cart	P	<p>This is marine audio at its best. This 8" two-way speaker is built specifically for marine use, with heavy-duty construction that strenuously resists water, corrosion, heat—and boring music. Pioneer research showed that more bass is required for boating applications, and this 8" size delivers.</p>
54	TS-W3002D2\|D4	CS-Cart	P	<p>The TS-W3002D2/4 subwoofers are designed to produce enormously powerful music. Just take a look at its super-heavy-duty construction, all designed to handle up to 3,500 watts max. Double-stacked magnets for higher power, and a step-up radial surround for superior control and long excursions. We used a constructed cast-aluminum basket for this sub. Add in our patented IMPP cone and you've got a monster sub that can handle whatever you want to throw at it.</p>
65	XRS 9370	CS-Cart	P	<p>The XRS 9370 provides total protection and peace of mind with the Xtreme Range Superheterodyne technology, detecting all 14 radar/laser bands at greater range than its predecessor. It comes in an industry-leading compact design with UltraBright™ Data Display, LaserEye®, and much more.</p>
64	XRS 9745	CS-Cart	P	<p>The XRS 9745 provides total protection and peace of mind with Xtreme Range Superheterodyne® Technology, detecting all 15 radar/laser bands with its super-fast lock-on detection circuitry. The unit provides extra detection range and the best possible advance warning to even the fastest of POP mode radar guns. Other features include DigiView® Text Display, an 8-point electronic compass, Voice Alert™, and much more.</p>