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 (177, 178, 179, 180, 181, 182) 
  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.00112

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "15.85"
    },
    "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": "range",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "rows_examined_per_scan": 6,
              "rows_produced_per_join": 0,
              "filtered": "4.00",
              "index_condition": "(`atulecarter_atul_demo1`.`cscart_categories`.`category_id` in (177,178,179,180,181,182))",
              "cost_info": {
                "read_cost": "14.36",
                "eval_cost": "0.05",
                "prefix_cost": "14.41",
                "data_read_per_join": "641"
              },
              "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.25",
                "eval_cost": "0.14",
                "prefix_cost": "14.81",
                "data_read_per_join": "11"
              },
              "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": "0.72",
                "eval_cost": "0.01",
                "prefix_cost": "15.67",
                "data_read_per_join": "300"
              },
              "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.06",
                "eval_cost": "0.01",
                "prefix_cost": "15.74",
                "data_read_per_join": "286"
              },
              "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": "15.85",
                "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
148	16GB A Series Walkman Video MP3	CS-Cart	P	<p>With an A Series by your side, there's never a dull moment. The dazzling 2.8" (400x240) touch screen delivers incredible color and quickly serves up photos, videos, album artwork and more. Easily navigate your media library with the tap of a finger or via traditional button controls if you prefer. Experience your music wirelessly and stream audio to other compatible devices with integrated Bluetooth®. Sony Clear Audio technologies work as a team to make your music sound the best it can – Digital Sound Enhancement Engine, Clear Stereo and Clear Bass Audio Technologies offer deeper, richer sound plus S-Master™ MX amplification makes for higher signal to noise ratios and less distortion. Upload the matching lyric file and learn the words to your favorite song as your A Series scrolls them along the screen in sync with the music. Premium EX earbuds and USB cable included.</p>
149	8GB E Series Walkman Video MP3	CS-Cart	P	<p>Long lasting battery offering up to 50 hours of music or up to 10 hours of video playback, FM tuner, Karaoke mode, Clear Audio technologies, EX headphones, integrated mic and 2.0" LCD.</p>
242	Apple iPod Classic Black	CS-Cart	P	<p>With 160GB of storage, iPod classic can hold up to 40,000 songs, 200 hours of video, or 25,000 photos.** That’s more than enough room for a day’s — or a lifetime’s — worth of entertainment.</p>
243	Apple iPod Classic White	CS-Cart	P	<p>With 160GB of storage, iPod classic can hold up to 40,000 songs, 200 hours of video, or 25,000 photos.** That’s more than enough room for a day’s — or a lifetime’s — worth of entertainment.</p>
134	Creative ZEN Style M300 MP3 Player 16GB (Black)	CS-Cart	P	<p>Don't let the compact appearance of the Creative ZEN Style M300 MP3 player fool you. For a media player this tiny, you’d be amazed at the array of features it can offer for a remarkably affordable price.</p>
135	Creative ZEN Touch 2 MP3 Player 8GB (Black)	CS-Cart	P	<p>The ZEN line of portable media players has long been associated with the highest quality in audio playback, and the Creative ZEN Touch 2 continues to uphold this standard. But that's not all it offers. What makes this player truly stand out from the rest is that you can stream music wirelessly to compatible Bluetooth® headphones or speakers such as Creative's ZiiSound D5 and WP-300, and surf the Internet too.</p>
156	Extra Bass Headphones – 70mm	CS-Cart	P	<p>Get that super bass. A tightly sealed acoustic design combines mega 70mm XB driver units – the world's largest1 – and king-sized cushion earpads to deliver ultra-deep bass.</p>
154	JR900 Wireless Headphones	CS-Cart	P	<p>Koss JR900s are the music lover’s answer to true stereo and cordless freedom. Operating on one of the highest power bands available on the market, the Koss JR900 headphone system (consisting of a high quality, battery operated RF transmitter with AC adapter) offers the best in wireless audio technology, allowing you to roam freely over a 70,000-square-foot area. From room to room, inside to outside, even in bright sunlight, you can move to your heart’s content without the need for additional transmitters or worry of signal breakup.</p>
155	Porta Pro KTC On-Ear Headphone	CS-Cart	P	<p>Koss Porta Pro KTC, the most sophisticated version of the Porta Pro, features a new technological upgrade which brings listeners closer to the music they love. Koss Touch Control (KTC) remote and microphone technology, made for iPhone®, iPad® and iPod®, gives listeners more control in fewer steps. Listeners are now able to stay connected, shuffle song and adjust volume with the flick of a switch.</p>
133	Samsung Galaxy Player 4.0	CS-Cart	P	<p>Easily connect via Wi-Fi without any monthly service fees. The Samsung Galaxy Player can access the internet and apps anywhere there is an available hotspot. The sleek, lightweight 4.27 oz. design fits in any pocket and the brilliant 4” Super Clear LCD delivers incredible viewing quality.</p>
132	Samsung Galaxy Player 5.0	CS-Cart	P	<p>Easily connect via Wi-Fi without any monthly service fees. The Samsung Galaxy Player can access the internet and apps anywhere there is an available hotspot. The sleek, lightweight 6.42 oz. design fits in any pocket and the brilliant 5” LCD delivers incredible viewing quality.</p>
146	Sansa Clip+ MP3 Player (Blue) - 4GB	CS-Cart	P	<p>Small but powerful, the Sansa Clip+ is a tiny MP3 player that boasts an array of cool features, as well as distinctively big sound for its small size. Rock out with up to 1,000 songs on this blue player. &dagger; Enjoy even more play when you use the expandable memory slot to play preloaded microSDHC™ cards. slotRadio™ and slotMusic cards are preloaded with tunes ready to pop into the player’s memory card slot. Rock on with a 15-hour battery life. &dagger; &dagger;</p>