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 = 'da' 
  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 (179) 
  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.00162

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "17.66"
    },
    "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": 10,
              "rows_produced_per_join": 10,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "1.23",
                "eval_cost": "2.00",
                "prefix_cost": "3.23",
                "data_read_per_join": "160"
              },
              "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": "10.00",
                "eval_cost": "0.17",
                "prefix_cost": "15.22",
                "data_read_per_join": "4K"
              },
              "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.85",
                "eval_cost": "0.17",
                "prefix_cost": "16.25",
                "data_read_per_join": "3K"
              },
              "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": 2,
              "filtered": "97.36",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.90",
                "eval_cost": "0.50",
                "prefix_cost": "17.66",
                "data_read_per_join": "59"
              },
              "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>
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>
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>
147	Sansa Clip+ MP3 Player (Red) - 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 red player. 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.</p>