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 (
    166, 174, 175, 176, 177, 178, 179, 180, 
    181, 182, 199, 185, 186, 187, 188, 189, 
    190, 191, 194, 193, 195, 196, 197, 198, 
    200, 202, 234, 235, 236, 237, 238, 240, 
    254, 255, 263
  ) 
  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.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  128, 32

Query time 0.00383

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "28.54"
    },
    "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": "6K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "ALL",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "rows_examined_per_scan": 84,
              "rows_produced_per_join": 1,
              "filtered": "1.67",
              "cost_info": {
                "read_cost": "19.92",
                "eval_cost": "0.28",
                "prefix_cost": "20.20",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "category_id",
                "storefront_id",
                "usergroup_ids",
                "status"
              ],
              "attached_condition": "((`pankajecarter_systemfour`.`cscart_categories`.`category_id` in (166,174,175,176,177,178,179,180,181,182,199,185,186,187,188,189,190,191,194,193,195,196,197,198,200,202,234,235,236,237,238,240,254,255,263)) and ((`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids` = '') or find_in_set(0,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`) or find_in_set(1,`pankajecarter_systemfour`.`cscart_categories`.`usergroup_ids`)) and (`pankajecarter_systemfour`.`cscart_categories`.`status` in ('A','H')) and (`pankajecarter_systemfour`.`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": [
                "pankajecarter_systemfour.cscart_categories.category_id"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 4,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "1.50",
                "eval_cost": "0.84",
                "prefix_cost": "22.54",
                "data_read_per_join": "67"
              },
              "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": [
                "pankajecarter_systemfour.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "7.93",
              "cost_info": {
                "read_cost": "4.20",
                "eval_cost": "0.07",
                "prefix_cost": "27.58",
                "data_read_per_join": "1K"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "usergroup_ids",
                "parent_product_id"
              ],
              "attached_condition": "((`pankajecarter_systemfour`.`products`.`parent_product_id` = 0) and (`pankajecarter_systemfour`.`products`.`company_id` = 1) and ((`pankajecarter_systemfour`.`products`.`usergroup_ids` = '') or find_in_set(0,`pankajecarter_systemfour`.`products`.`usergroup_ids`) or find_in_set(1,`pankajecarter_systemfour`.`products`.`usergroup_ids`)) and (`pankajecarter_systemfour`.`products`.`status` = 'A') and (`pankajecarter_systemfour`.`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": [
                "pankajecarter_systemfour.products_categories.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 0,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.33",
                "eval_cost": "0.07",
                "prefix_cost": "27.98",
                "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": [
                "pankajecarter_systemfour.products_categories.product_id"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 0,
              "filtered": "97.92",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.36",
                "eval_cost": "0.20",
                "prefix_cost": "28.54",
                "data_read_per_join": "23"
              },
              "used_columns": [
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "attached_condition": "((`pankajecarter_systemfour`.`prices`.`lower_limit` = 1) and (`pankajecarter_systemfour`.`prices`.`usergroup_id` in (0,0,1)))"
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_type	full_description
400	test123	CS-Cart	P
401	test123	CS-Cart	P
402	test123	CS-Cart	P
403	test123	CS-Cart	P
404	test123	CS-Cart	P
405	test123	CS-Cart	P
406	test123	CS-Cart	P
407	test123	CS-Cart	P
408	test123	CS-Cart	P
409	test123	CS-Cart	P
410	test123	CS-Cart	P
411	test123	CS-Cart	P
412	test123	CS-Cart	P
413	test123	CS-Cart	P
414	test123	CS-Cart	P
415	test123	CS-Cart	P
416	test123	CS-Cart	P
417	test123	CS-Cart	P
418	test123	CS-Cart	P
419	test123	CS-Cart	P
420	test123	CS-Cart	P
421	test123	CS-Cart	P
422	test123	CS-Cart	P
423	test123	CS-Cart	P
424	test123	CS-Cart	P
425	test123	CS-Cart	P
426	test123	CS-Cart	P
68	Titan	CS-Cart	P	<p>In the right hands, amazing things happen.<br /><br />Put emails in their place<br /> Combine your personal inboxes while keeping your work account separate.<br /><br />Plenty of room in the office<br /> Microsoft® Office Mobile and multitasking on a huge <br /> 4.7" screen makes plenty of room for work, wherever you go.<br /><br />Focus on thoughts, not thumbs<br /> With such a spacious keypad, you can stop focusing on your fingers and start focusing on ideas.<br /><br />Expansive display, infinite possibility<br /> Whether you’re taking care of business or taking a break from it all, you can see the big picture on the 4.7" LCD screen.<br /><br />Pocket-sized profile<br /> At 9.9mm, it’s slim enough to fit comfortably in your pocket. But considering everything the HTC TITAN with Windows Phone is capable of, it won’t be spending much time there.</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>
6	Toshiba 32C120U 32" Class 720P HD LCD TV	CS-Cart	P	<p>Think you need to break your budget to enjoy blockbuster HD entertainment? Not with Toshiba's 32" class C110U LCD HDTV. Featuring all-new design, this value-packed television looks great and works well in a living room, kitchen or bedroom. Increase detail and depth of the images on your screen with our impressive Dynalight technology.</p>
8	Toshiba 32TL515U 32" Class 1080P 3D LED HD TV	CS-Cart	P	<p> Although its attractive features, design, and passive 3D have merit, the picture quality issues of the Toshiba TL515U LED-based LCD TV lessen its appeal in the face of the stiff competition. </p>
14	Toshiba 40E220U 40" Class 1080P HD LCD TV	CS-Cart	P	<p>Enjoy a stunning picture bursting with color and clarity with the 40-inch Toshiba 40E200U LCD HDTV (40-inch diagonal screen size), featuring a 1080p Full HD resolution that provides twice the pixel resolution of 720p HD models. It provides a number of convenient home entertainment options and key picture quality features, including DynaLight dynamic backlight control for deeper black levels and four HDMI digital inputs for simple high quality connection to cable/satellite boxes, DVD players and more. Plus, the HDMI ports feature fast InstaPort switching for quick changes from one source to another. <br /><br />DynaLight automatically adjusts the backlight intensity based on the image content.<br /><br /> With the Gaming Mode function, you'll experience reduced game control delay and improved reaction time--perfect for PC gaming--and the Photo Frame capability with Auto Slide Show enables you to display slideshows of your favorite images when not watching TV programming. <br /><br /> Boasting a slimming tapered bezel and attractive front panel gradation, the new Horizon design creates a more modern, elegant appearance that also adds a subtle luminance to the logo to further its stylish appeal. Other features include MP3 playback via the integrated USB port, a PC input, and a removable stand.</p>