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 (209) 
  AND products.product_id NOT IN (129) 
  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.00097

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": 2,
              "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` = 209) and (`atulecarter_atul_demo1`.`products_categories`.`product_id` <> 129))"
            }
          },
          {
            "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	parent_product_id	full_description
131	Catania	CS-Cart	P	0	<p class="MsoPlainText">The new Catania features carbon fork, carbon seatpost, Ultegra derailleur, and the new Fezzari Racing Design R3A frame with increased aerodynamic properties and a sleeker look.</p> <p class="MsoPlainText">The Catania road bike is a great all-around bike. If you want to get into road biking or want a bike that is durable, high-performing, and as comfortable as a road bike can get, the Catania is for you.</p> <p class="MsoPlainText">The Catania features the R3A frame with compact geometry. Compact geometry means that we shorten the seat tube slightly, which makes the bike a little lighter and reduces lateral flex (you don’t want your bike flexing side to side when you’re pedaling). It also allows your riding position to be a little more upright.</p> <p class="MsoPlainText">Add to that a great set of components, including: Shimano Ultegra derailleur and Shimano 105 shifters for smooth shifting Fezzari Carbon II fork to minimize the road chatter; FSA Gossamer crank with external oversized bearings that make it pedal smoother and more efficiently; Alex double-wall rims that are extremely durable; Maxxis performance tires; and a Fezzari Racing Design Ultra XR saddle that is one of the most comfortable road saddles you’ll ride.</p>
127	Fore CR5 SRAM Red	CS-Cart	P	0	<p>An all new frame and new graphics package make the Fore CR5 look as fast as it really is. Now available with SRAM Red componants.</p> <p>What do you get when you interview hundreds of riders, questioning them about their desires for the ultimate road bike and then, spend thousands of hours translating those ideas into reality? A dream come true. We call it the Fezzari Foré CR5. It is the pinnacle of road bike performance. From its all new state of the art, proprietary Fezzari Racing Design Xr7 3K monocoque carbon frame to its best-of-market components, the Foré CR5 is THE ultimate road bike. Our designers and engineers didn't cut corners.<br /> <br /> Take, for instance, the state of the art proprietary Fezzari Racing Design Xr7 3K monocoque carbon fiber frame. We used the highest grade of carbon fiber available on the market. We made it even stiffer laterally by enlarging the bottom bracket shell and adding the BB30 bottom bracket/crank design, strengthening the headtube and making it more laterally stiff by enlarging the diameter to a 1.5" tapered design. We've also enlarged the seatstays near the rear axle for more lateral rigidity. We made the Xr7 frame even more vertically compliant for those rides on the cobblestones. We've done this by using an oval design in our top tube and down tube.  The new Xr7 frame is even more aerodynamic than before with the new F4 ultralight bladed carbon fork and internal cable routing on the top tube.  The Xr7 frame was recently tested to be one of the most durable frames on the market, and it weighs a mere 900 grams. Strength, lightweight, rigidity where you need it most, and flexible enough to take the edge off of rough roads -- it's the perfect frame for anyone who wants to get the most out of their riding experience.<br /> <br /> Next, take a look at the perfect blend of components. The Fore CR5 includes SRAM Red components shifters, brakes and derraileurs, FSA K-Force Light BB30 Carbon Crankset, Fezzari F4 XrT 3K carbon ultralite bladed carbon fork with carbon steerer tube, FSA K-Force compact carbon bars, FSA K- Force carbon seat post, and even carbon spacers on the headset.<br /> <br /> Finally, add the new sleek graphics and logos and you have--the ultimate road ride.<br /> <br /> You get the following upgrades over the CR3:</p> <ol> <li> <strong>Upgraded Frameset</strong> – from the Xr5 to the new Xr7 frame,  more lateral stiffness with a BB30 crank system, enlarged headtube for better cornering, and sprinting, added rigidity in the rear triangle, increased aerodynamics with the new F4 bladed fork design and internal cable routing.</li> <li> <strong>Upgraded Shifters</strong> – from Shimano Ultegra to the flagship SRAM Red, for smoother, more precise shifting</li> <li> <strong>Upgraded Front Derailleur</strong> – from Shimano Ultegra to SRAM Red, which is lighter and smoother</li> <li> <strong>Upgraded Cassette</strong> – from Shimano Ultegra to SRAM Red</li> <li> <strong>Upgraded Crankset</strong> – from FSA SLK Light to FSA K-Force Light, which is lighter</li> <li> <strong>Upgraded Chain</strong> – from Shimano Ultegra to SRAM Red</li> <li> <strong>Upgraded Wheelset</strong> – Mavic Ksyrium SL, which are lighter,stiffer, faster, and perform better</li> <li> <strong>Upgraded Brakes</strong> – from Shimano Ultegra to SRAM Red, which are lighter and smoother</li> <li> <strong>Upgraded Seatpost-  </strong>The  FSA K-force light seatpost is one of the lightest on the market.  It also completes the graphics package complimenting the crank and handle bar.</li> </ol> <p>When you get a Foré CR5, we custom fit it for you through our 23-Point Custom Setup program. We’ll get your specific  body measurements so we can outfit your bike to fit you perfectly.  You'll have the rest of the peloton envious of your ride.</p>