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 (238) 
  AND products.product_id NOT IN (86) 
  AND companies.status IN ('A') 
  AND (
    products.amount > 0 
    OR products.tracking = 'D'
  ) 
  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, 4

Query time 0.00177

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6.10"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "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": "range",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id",
                "product_id"
              ],
              "key_length": "6",
              "rows_examined_per_scan": 5,
              "rows_produced_per_join": 3,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "1.05",
                "eval_cost": "0.60",
                "prefix_cost": "1.65",
                "data_read_per_join": "48"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ],
              "attached_condition": "((`atulecarter_atul_demo1`.`products_categories`.`category_id` = 238) and (`atulecarter_atul_demo1`.`products_categories`.`product_id` <> 86))"
            }
          },
          {
            "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": "17.03",
              "cost_info": {
                "read_cost": "3.00",
                "eval_cost": "0.10",
                "prefix_cost": "5.25",
                "data_read_per_join": "2K"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "amount",
                "usergroup_ids",
                "tracking",
                "parent_product_id"
              ],
              "attached_condition": "((`atulecarter_atul_demo1`.`products`.`parent_product_id` = 0) and ((`atulecarter_atul_demo1`.`products`.`amount` > 0) or (`atulecarter_atul_demo1`.`products`.`tracking` = 'D')) 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.51",
                "eval_cost": "0.02",
                "prefix_cost": "5.87",
                "data_read_per_join": "613"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ],
              "attached_condition": "(`atulecarter_atul_demo1`.`companies`.`status` = 'A')"
            }
          },
          {
            "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": "29.30",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.09",
                "eval_cost": "0.01",
                "prefix_cost": "6.01",
                "data_read_per_join": "1"
              },
              "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)))"
            }
          },
          {
            "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": "6.10",
                "data_read_per_join": "349"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product",
                "full_description"
              ]
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_type	full_description
106	Nokia E5-00	CS-Cart	P	<p>Discover more ways to stay connected with friends, family, business contacts and everyone important to you with the Nokia E5-00. It has everything you need to stay in touch with a variety of email and messaging options, while also helping you to take and share pics and videos, explore the web, connect through social networking, access work and personal email, navigate your day and more. The affordable Nokia E5 even manages to fit a full QWERTY keyboard into its compact, eye-catching design for quick and easy messaging on the go. Plus, there are plenty of features to make it the perfect business-focused device. Stay productive and stay in the loop with work no matter where you are with simple setup and access for your corporate email account. The Nokia E5 makes communicating with work a breeze with Mail for Exchange, the free, direct and secure corporate email solution that offers a robust desktop-like email experience right on your phone, with email, calendar, contacts and more. Plus, your work email shows up on your phone instantly, so you don’t have to wait for important emails. The Nokia E5 also features a fully integrated Microsoft Communicator Mobile client, to provide your mobile device the same great IM, contacts and usability that you enjoy on your computer. Keep in touch however you like with a variety of ways to connect to others with the Nokia E5. The home screen offers an easy visual way to view your contacts and makes it easy to organize the people you talk to most. The Nokia E5 also features one-click access to messaging as well as easy email setup and support for plenty of popular personal email services. The leading IM services have also been seamlessly integrated onto the phone. The Nokia E5 also offers free voice-guided navigation from Ovi Maps so you always know where you’re going. Plus customize your phone with thousands of available apps from Ovi Store. Connect with everyone and everything you want with the Nokia E5.</p>
105	Nokia Lumia 800	CS-Cart	P	<p>EASIER, FASTER, FUNNER<br /><br />The Nokia Lumia 800. One glance to see what everyone's up to.</p>
101	Nokia X3-02	CS-Cart	P	<p>Stay connected and keep yourself entertained wherever you roam with the sleek Nokia X3-02 unlocked GSM mobile phone. This stunning, touch-and-type phone features a keypad for easy messaging and a touch display for one-handed navigation. Touch and type is ideal for SMS and social networking where fast and frequent input is needed, while touch is ideal for functions such as setting alarms, smooth browsing and controlling applications like music and games.</p>