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 
  0, 12

Query time 0.00151

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
59	AVH-P4400BH	CS-Cart	P	<p>The AVH-P4400BH DVD multimedia receiver features a 7" motorized touchscreen display, built-in Bluetooth® for hands-free calling and audio streaming, and an HD Radio™ tuner. The newly updated and innovative user interface makes direct operation of the unit stress-free by eliminating complicated layered selection displays, so access to desired functions is smoother and simpler than ever. A variety of multimedia options ensures that you will be properly entertained. Customizable audio and video functions make it easy to suit your mood.</p>
58	AVH-P8400BH	CS-Cart	P	<p>The AVH-P8400BH is Pioneer's top-of-the-line DVD multimedia receiver that features a beautiful 7" motorized touchscreen interface with our new Advanced Display Resolution technology, built-in Bluetooth® for hands-free calling and audio streaming, an HD Radio™ tuner, and Advanced App Mode for iPhone® 4/4S. The newly designed and innovative user interface makes direct operation of the unit stress-free by eliminating complicated layered selection displays, so access to desired functions is smoother and simpler than ever. A variety of multimedia options ensures that you will be properly entertained. Customizable audio and video functions make it easy to suit your mood.</p>
60	AVIC-Z130BT	CS-Cart	P	<p>Pioneer's flagship navigation receiver, the double-DIN AVIC-Z130BT features built-in navigation with maps of the US, Alaska, Hawaii, Canada, and Puerto Rico. Get there fast with spoken street names, lane guidance, and a wealth of GPS features, and stay in touch on the way with built-in Bluetooth for hands-free calling. The motorized 7-inch WVGA touchscreen display is great for GPS and for navigating your entertainment, whether it's DVDs or CDs, your digital media from USB drives or microSD cards, or content from your iPhone. Speaking of iPhones, the AVIC-Z130BT features enhanced integration with Pandora and Aha Radio apps, along with GPS features through the free AVIC FEEDS app.</p>
42	DEH-1300MP	CS-Cart	P	<p>With music in so many different formats, it only makes sense to have a CD receiver that can play them all. Take your digital songs on the go by copying them directly onto a CD-R or CD-RW disc in either MP3 or WMA formats. Since these files are digitally encoded, you will be able to see detailed track information such as artist, song title, and album.</p>
39	DEH-80PRS	CS-Cart	P	<p>The DEH-80PRS CD receiver features audiophile grade internal components and materials that are carefully selected to achieve Pioneer's highest standards for the ultimate in sound quality. Pioneer’s exclusive sound field technologies including Auto EQ and Auto Time Alignment help optimize audio in ways that perfectly suit particular listening spaces and allow subtle manual control of settings. Versatile connectivity helps deliver exceptional sound quality from various digital devices.</p>
40	DEH-P8400BH	CS-Cart	P	<p>The DEH-P8400BH CD receiver features a full-dot LCD display with Pioneer's new MIXTRAX technology, built-in Bluetooth® for hands-free calling and audio streaming, and an HD Radio™ tuner. A variety of multimedia options ensures that you will be properly entertained. Customizable audio functions make it easy to suit your listening taste.</p>
37	DPX308U	CS-Cart	P	<p>Many cars come with a 2DIN opening so swap that basic factory radio out for a DPX308U! This full featured CD-receiver comes with front USB input which provides a simple connection for your iPod/iPhone, allowing you to listen to your favorite music while controlling it easily from you your car stereo. When connecting an iPod or iPhone to the USB port you get the added benefit of advanced search features designed to let you spend more time listening to your favorite songs instead of hunting for them.</p>
43	FH-P8000BT	CS-Cart	P	<p>The FH-P8000BT is smartly designed to expand your system, customize your experience, provide superior sound quality and easy operation.</p>
56	GM-6500F	CS-Cart	P	<p>From dramatic distinction to dramatic design, Pioneer's new GM-Series amplifiers are thrillers in every sense, pushing the limits of flexibility and functionality.<br /><br />The 4-channel GM-6500F delivers a whopping 760 watts max - plenty of juice to power your speakers. It's also got built-in low-pass/high-pass crossovers, speaker level inputs for easy connection to an OEM headunit and oversized power terminals.</p>
57	GM-D8500M	CS-Cart	P	<p>Class-D amplifiers overcome the inefficiencies of traditional Class-A or AB amplifiers. Pioneer’s Class-D amps transform very little power into heat so a higher percentage of the power supply (67%) is transformed into the load. This results in a very compact amp which needs less input power to produce very high output power. The Class-D amplifier’s PWM (Pulse Width Modulator) modulates the original audio input signal with a triangulated signal wave which has a much higher fixed frequency. The result is a digital signal which contains both the input signal and a band of frequency components around the modulation frequency. A LPF (Low Pass Filter) then filters out the high frequency pulses and the resulting amplified output signal is then sent to the subwoofer and/or speakers.</p>
30	GO 2535 TM WTE	CS-Cart	P	<p>The TomTom GO series is the ultimate in driving intelligence. The new, super-slim TomTom GO series holds the latest navigation technology, in a fresh, sleek design and includes a magnetic, click & lock mount for maximum mounting security. Travel confidently with superior routing and the most accurate, dependable maps in the GPS industry.</p>
53	KFC-W3012	CS-Cart	P	<p>Super-sturdy, super powerful, the KFC-W3012 is a 12-inch, 4-ohm sub that handles an impressive 1200W of power. A truly high-performance sub, the KFC-W3012 has a long stroke design and a silver textured pattern cone woofer that adds strength and rigidity to the cone for maximum bass performance.</p>