SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  products.product_type, 
  products.parent_product_id 
FROM 
  q_products as products 
  LEFT JOIN q_product_options_inventory as inventory ON inventory.product_id = products.product_id 
  LEFT JOIN q_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'en' 
  LEFT JOIN q_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  LEFT JOIN q_companies AS companies ON companies.company_id = products.company_id 
  INNER JOIN q_products_categories as products_categories ON products_categories.product_id = products.product_id 
  INNER JOIN q_categories ON q_categories.category_id = products_categories.category_id 
  AND (
    q_categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, q_categories.usergroup_ids) 
    OR FIND_IN_SET(1, q_categories.usergroup_ids)
  ) 
  AND q_categories.status IN ('A', 'H') 
WHERE 
  1 
  AND companies.status = 'A' 
  AND products.company_id = 2 
  AND (
    CASE products.tracking WHEN 'O' THEN inventory.amount > 0 WHEN 'B' THEN products.amount > 0 ELSE 1 END
  ) 
  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 = 2 
  AND products.parent_product_id = 0 
GROUP BY 
  products.product_id 
ORDER BY 
  products.timestamp desc, 
  products.product_id ASC 
LIMIT 
  1152, 128

Query time 0.10344

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7419.44"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "inventory",
              "access_type": "system",
              "possible_keys": [
                "pc"
              ],
              "rows_examined_per_scan": 0,
              "rows_produced_per_join": 1,
              "filtered": "0.00",
              "const_row_not_found": true,
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "0.20",
                "prefix_cost": "0.00",
                "data_read_per_join": "976"
              },
              "used_columns": [
                "product_id",
                "amount"
              ]
            }
          },
          {
            "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": "5K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ]
            }
          },
          {
            "table": {
              "table_name": "products_categories",
              "access_type": "index",
              "possible_keys": [
                "PRIMARY",
                "pt"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id",
                "product_id"
              ],
              "key_length": "6",
              "rows_examined_per_scan": 15659,
              "rows_produced_per_join": 15659,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "140.59",
                "eval_cost": "3131.80",
                "prefix_cost": "3272.39",
                "data_read_per_join": "244K"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "q_categories",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "c_status",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "portal.products_categories.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 3131,
              "filtered": "20.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "626.36",
                "prefix_cost": "6404.19",
                "data_read_per_join": "8M"
              },
              "used_columns": [
                "category_id",
                "usergroup_ids",
                "status"
              ],
              "attached_condition": "(((`portal`.`q_categories`.`usergroup_ids` = '') or find_in_set(0,`portal`.`q_categories`.`usergroup_ids`) or find_in_set(1,`portal`.`q_categories`.`usergroup_ids`)) and (`portal`.`q_categories`.`status` in ('A','H')))"
            }
          },
          {
            "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": [
                "portal.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 277,
              "filtered": "8.87",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "55.56",
                "prefix_cost": "7030.55",
                "data_read_per_join": "1M"
              },
              "used_columns": [
                "product_id",
                "product_type",
                "status",
                "company_id",
                "amount",
                "timestamp",
                "usergroup_ids",
                "tracking",
                "parent_product_id"
              ],
              "attached_condition": "((`portal`.`products`.`parent_product_id` = 0) and (`portal`.`products`.`company_id` = 2) and (case `portal`.`products`.`tracking` when 'O' then <cache>((NULL > 0)) when 'B' then (`portal`.`products`.`amount` > 0) else 1 end) and ((`portal`.`products`.`usergroup_ids` = '') or find_in_set(0,`portal`.`products`.`usergroup_ids`) or find_in_set(1,`portal`.`products`.`usergroup_ids`)) and (`portal`.`products`.`status` = 'A'))"
            }
          },
          {
            "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": [
                "portal.products_categories.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 277,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.00",
                "eval_cost": "55.56",
                "prefix_cost": "7086.11",
                "data_read_per_join": "1M"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product"
              ]
            }
          },
          {
            "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": [
                "portal.products_categories.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 277,
              "filtered": "99.95",
              "using_index": true,
              "cost_info": {
                "read_cost": "277.78",
                "eval_cost": "55.53",
                "prefix_cost": "7419.44",
                "data_read_per_join": "6K"
              },
              "used_columns": [
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "attached_condition": "((`portal`.`prices`.`lower_limit` = 1) and (`portal`.`prices`.`usergroup_id` in (0,0,1)))"
            }
          }
        ]
      }
    }
  }
}

Result

product_id product company_name product_type parent_product_id
37513 360 Perry Ellis Men 30ml+Red EDT 30ml+Reserve EFT 30ml Set Smell Attractive Perfumes P 0
37514 360 Perry Ellis Women EDT 100ml+118ml B.Mist+57G H.Cream+10ml Set Smell Attractive Perfumes P 0
37515 360 Perry Ellis Women EDT 30ml +360 Coral Women EDP 30ml+360 Purple EDP Women 30ml Set Smell Attractive Perfumes P 0
37516 4 Legend Forty EDP 100ml + Deo. 200ml Set Smell Attractive Perfumes P 0
37520 Alexandre J. The Collector Morning Muscs+Rose Oud+Silver Ombre (3 X 8ml) Mini Set Smell Attractive Perfumes P 0
37521 Amouage Memoir Bath 100m BL + 100ml HC 100ml SG Gift Set For Women Smell Attractive Perfumes P 0
37522 Antonio Banderas Blue Seduction Men EDT 100ml+150ml Deodorant Set Smell Attractive Perfumes P 0
37523 Antonio Banderas Her Secret Temptation EDT 80ml+150ml Deodorant Set Smell Attractive Perfumes P 0
37524 Antonio Banderas King Of SeductionMen EDT 100ml+75ml ABS Set Smell Attractive Perfumes P 0
37525 Antonio Banderas Queen Of Seduction 80ml+150ml Deodorent Set Smell Attractive Perfumes P 0
37526 Aramis Miniature Men Aramis Eau De Toilette 7ml Aftershave 7ml Black Eau De Toilette 7ml Voyager Eau De Toilette 7ml Smell Attractive Perfumes P 0
37527 Atelier Cologne Vetiver Fatal Absolue EDP 100ml+30ml+Leather Case Travel Set Smell Attractive Perfumes P 0
37528 Azzaro Pour Homme EDT 100ml+150ml Deodrant Travel Set Smell Attractive Perfumes P 0
37529 Azzaro Wanted Men EDT 100ml+150ml Deodorant Travel Set Smell Attractive Perfumes P 0
37530 Bentley Momentum EDT 100ml+200ml Shower Gel Set Smell Attractive Perfumes P 0
37531 Beyonce Heat EDP 15ml+ Rise EDP 30ml+ Heat Rush EDT 15ml Set Smell Attractive Perfumes P 0
37532 Boucheron Iris De Syracuse EDP 125ml+Santal De Kandy EDP 125ml+Ambre D'Alexandrie EDP 125ml Set Smell Attractive Perfumes P 0
37533 Britney Spears Fantasy Women EDP 100ml+100ml Body Lotion Set Smell Attractive Perfumes P 0
37534 Britney Spears Fantasy EDP 30ml+Curious EDP 30ml+Fantasy Stage Edition EDP 30ml Set Smell Attractive Perfumes P 0
37535 Burberry Burberry Her EDP 50ml+6ml Liquid LIP Velvet Fawn Set Smell Attractive Perfumes P 0
37536 Burberry Mr Burberry EDT 2X30ml Travel Set Smell Attractive Perfumes P 0
37537 Burberry Mr Burberry Indigo EDT 5ml+ Mr Burberry EDP 5ml+ Mr Burberry EDT 2 X 5ml Mini Set Smell Attractive Perfumes P 0
37538 Burberry My Burberry Women EDP 90ml+6ml Kisses Gloss Sugar Pink+3.5ml Mascara Set Smell Attractive Perfumes P 0
37539 Burberry My Burberry Women EDT 5ml+EDP 5ml+My Burberry Black EDP 5ml+Blush EDP 5ml Mini Set Smell Attractive Perfumes P 0
37540 Burberry My Burberry Women 5ml+My Burberry EDP 2 X 5ml+My Burberry Black 5ml Mini Set Smell Attractive Perfumes P 0
37541 Burberry My Burberry BlackWomen EDP 50ml+75ml Bl Set Smell Attractive Perfumes P 0
37542 My Burberry Black EDP+ 75ml Travel Women Smell Attractive Perfumes P 0
37543 Burberry My Burberry Blush Women EDP 90ml+3.3G Lipstick Set Smell Attractive Perfumes P 0
37544 Burberry My Limited Edition EDP 90ml+30ml Body Oil+10G Gold Glow Powder Set Smell Attractive Perfumes P 0
37545 Bvlgari Aqva Men's Deodorant Stick 75ml Smell Attractive Perfumes P 0
37546 Bvlgari Aqva Divina Women EDT 65ml+100ml Bl+150G Soap+100ml Sg Set Smell Attractive Perfumes P 0
37547 Bvlgari Aqva Divina Women EDT 65ml+40ml Bl+40ml Sg+50G Soap+Pouch Set Smell Attractive Perfumes P 0
37548 Bvlgari Aqva Pour Homme EDT 3X15ml Refillable Travel Set Smell Attractive Perfumes P 0
37549 Bvlgari Blv Men EDT 100ml+75ml Asb+75ml Sg+Pouch Set Smell Attractive Perfumes P 0
37550 Bvlgari Goldea EDP 50ml+25ml+Mirror Set Smell Attractive Perfumes P 0
37551 Goldea The Roman Night by Bvlgari Gift Set for Women - Eau de Parfum Smell Attractive Perfumes P 0
37552 Bvlgari Goldea The Roman Night Set EDP Smell Attractive Perfumes P 0
37553 Bvlgari Goldea The Roman Night EDP 75ml+75ml Bl+75ml Shower Gel +Pouch Set Smell Attractive Perfumes P 0
37554 Bvlgari Man Black Cologne EDT 100ml+75ml Asb+75ml Sg+Pouch Set Smell Attractive Perfumes P 0
37555 Bvlgari Man Extreme EDT 15ml+Bvlgari In Black EDP 15ml+Bvlgari Man EDT 15ml Mini Set Smell Attractive Perfumes P 0
37557 Bvlgari Man In Black EDP 100ml+75ml Deo Stick Set Smell Attractive Perfumes P 0
37558 Bvlgari Man In Black EDP 15ml+Man Extreme 15ml+Man Black Cologne 15ml Mni Set Smell Attractive Perfumes P 0
37559 Bvlgari Man In Black EDP 60ml+40ml ASB +40ml Shower GelSet Smell Attractive Perfumes P 0
37560 Bvlgari Omnia Amethyste Women 65ml+15ml Mini Set Smell Attractive Perfumes P 0
37561 Bvlgari Omnia Amethyste Women EDT 65ml+75ml Bl+75ml Bl+Pouch Set Smell Attractive Perfumes P 0
37562 Bvlgari Omnia Coral Women 65ml+15ml+ Pouch Set Smell Attractive Perfumes P 0
37563 Bvlgari Omnia Paraiba Women EDT 65ml+15ml Mini Set Smell Attractive Perfumes P 0
37820 Guess 1981 Body Spray 226 ml, For Men Smell Attractive Perfumes P 0
37821 Guess Dare Body Spray 226 ml, For Men Smell Attractive Perfumes P 0
37822 Guess Girl 250 ml Body Mist, For Women Smell Attractive Perfumes P 0
37823 Guess Girl Belle 250 ml Body Mist, For Women Smell Attractive Perfumes P 0
37824 Guess Perfume for Women Pink Bottle EDP Smell Attractive Perfumes P 0
37825 Guess Seductive Body Spray 226 ml, For Men Smell Attractive Perfumes P 0
37826 Guess Seductive 250 ml Body Mist, For Women Smell Attractive Perfumes P 0
37827 Guess Seductive Blue Body Spray 226 ml Smell Attractive Perfumes P 0
37828 Fogg Happiness 250 ml Deodorant for Men Smell Attractive Perfumes P 0
37829 Hermes Eau Des Merveilles Deodorant 100 ml Smell Attractive Perfumes P 0
37830 Hermes Savon Des Merveilles 100 Gm Bath Soap Smell Attractive Perfumes P 0
37831 Hermes Voyage D'hermes After Shave Balm 100 ml Smell Attractive Perfumes P 0
37832 Park Avenue Hero Classic Deo Smell Attractive Perfumes P 0
37833 Hind Maroon Petite 3 ml Concentrated Oil Smell Attractive Perfumes P 0
37834 Hugo Boss Bottled Unlimited Deo Stick 75 ml, For Men Smell Attractive Perfumes P 0
37835 Hugo Boss Bottled Unlimited Deodorant 150 ml, For Men Smell Attractive Perfumes P 0
37836 Hugo Boss Bottled Unlimited Shower Gel 150 ml, For Men Smell Attractive Perfumes P 0
37837 Hugo Boss Iced Shower Gel 200 ml, For Men Smell Attractive Perfumes P 0
37838 Hugo Boss Red Shower Gel 200 ml Smell Attractive Perfumes P 0
37839 Hugo Boss the Scent Shower Gel 200 ml Smell Attractive Perfumes P 0
37840 Hugo Boss the Scent Body Lotion 200 ml Smell Attractive Perfumes P 0
37841 Hugo Boss the Scent Shower Gel150 ml, For Men Smell Attractive Perfumes P 0
37842 Dove Invisible Dry 48h Anti-Perspirant Deodorant, 250ml Smell Attractive Perfumes P 0
37843 Joop All About Eve Shower Gel 150 ml, For Women Smell Attractive Perfumes P 0
37844 Joop Homme Mild Deodorant 75 ml, For Men Smell Attractive Perfumes P 0
37845 Joop Homme Wild Shower Gel 150 ml, For Men Smell Attractive Perfumes P 0
37846 Kenneth Cole Blue Shower Gel 100 ml Smell Attractive Perfumes P 0
37847 Korloff Paris Royal Oud Deodorant 150 ml, For Men Smell Attractive Perfumes P 0
37848 Lacoste L'Homme Deodrant 150 ml, For Men Smell Attractive Perfumes P 0
37849 Lacoste Pour Femme Shower Gel 150 ml, For Women Smell Attractive Perfumes P 0
37850 Lalique Amethyst Body Cream 200 ml, For Men Smell Attractive Perfumes P 0
37851 Lalique Amethyst Eclat Body Cream 200 ml, For Women Smell Attractive Perfumes P 0
37852 Lalique L'Amour Body Cream 200 ml, For Women Smell Attractive Perfumes P 0
37853 Lalique Living 200 ml, Body Cream, For Women Smell Attractive Perfumes P 0
37854 Lancome La Vie Est Belle Body Lotion 200 ml, For Women Smell Attractive Perfumes P 0
37855 Lancome La Vie Est Belle Shower Gel 200 ml Smell Attractive Perfumes P 0
37856 Lancome Rose Sugar Scrub 100Ml Smell Attractive Perfumes P 0
37858 LUCKY BRAND Lucky You Fragrance Mists 236 ml Smell Attractive Perfumes P 0
37859 Miu Miu Body Cream 150 ml For Women Smell Attractive Perfumes P 0
37860 Miu Miu Body Lotion 200 ml, For Women Smell Attractive Perfumes P 0
37861 Miu Miu Shower Gel 200 Ml Smell Attractive Perfumes P 0
37862 Mont Blanc Legend After Shave 100 ml Smell Attractive Perfumes P 0
37863 Mont Blanc Legend Shaving Foam 100 Ml Smell Attractive Perfumes P 0
37864 Mont Blanc Legend Shower Gel 100 ml, For Men Smell Attractive Perfumes P 0
37865 Mont Blanc Legend Night Men After Shave Balm 100Ml Smell Attractive Perfumes P 0
37866 Mont Blanc Legend Spirit Men After Shave Balm 100Ml Smell Attractive Perfumes P 0
37867 Montale Paris Powder Flowers 100ml Hair Mist Smell Attractive Perfumes P 0
37868 Montale Paris Roses Elixir 100Ml Hair Mist Smell Attractive Perfumes P 0
37869 Montale Paris White Musk 100ml Hair Mist Smell Attractive Perfumes P 0
37870 Montegrappa Nerouno Men After Shave Balm 125Ml Smell Attractive Perfumes P 0
37871 Narciso Rodriguez Narciso Women Shower Gel 200Ml Smell Attractive Perfumes P 0
37872 Narciso Rodriguez W 30 Ml Hair Mist Smell Attractive Perfumes P 0
37873 Nina Ricci Deodorant Spray for Women, 150ml Smell Attractive Perfumes P 0
37874 Nina Ricci L'Extase Rose Absolue 30Ml Hair Mist Smell Attractive Perfumes P 0
37875 Octavia Pure Perfumed Deodorant Spray By Paris Corner For Men 250ML-SME Smell Attractive Perfumes P 0
37877 Orchid Noir 250 ml Deo. Smell Attractive Perfumes P 0
37878 Paco Rabanne 1 Million Shower Gel 150ml Smell Attractive Perfumes P 0
37879 Paco Rabanne Black XS L'Excess Men Shower Gel 100Ml Smell Attractive Perfumes P 0
37880 Paco Rabanne Black Xs L'exces Deodorant Spray - For Women(250 ml) Smell Attractive Perfumes P 0
37881 Paco Rabanne Pour Homme, Aftershave Lotion, 100ml Smell Attractive Perfumes P 0
37882 Paco Rabanne Invictus Deodorant Spray For Men Smell Attractive Perfumes P 0
37883 Paco Rabanne Olympea Bodylotion for Women 200 ml Smell Attractive Perfumes P 0
37884 Penhaligon'S Lavandula Hand Wash 300Ml Smell Attractive Perfumes P 0
37885 Penhaligon'S Lily Of The Valley Hand & Body Cream 150Ml Smell Attractive Perfumes P 0
37886 Porsche Design Palladium Deodorant Stick for Men 75 ml Smell Attractive Perfumes P 0
37887 Porsche Design Sport Deodorant Stick 75ml 2,7 Oz Smell Attractive Perfumes P 0
37888 Porsche Design Sport Hair & Body Shampoo 6.8oz 200ml Smell Attractive Perfumes P 0
37889 Porsche Design - The Essence For Men Deodorant Stick Smell Attractive Perfumes P 0
37890 Prada La Femme Satin Shower Cream 200ml/6.8oz Smell Attractive Perfumes P 0
37891 Prada Milano L'Homme Shower Cream 200Ml Smell Attractive Perfumes P 0
37892 Prada Pour Homme After Shave Balm 50Ml Smell Attractive Perfumes P 0
37893 Paradiso Azzurro by Roberto Cavalli Perfumed Shower Gel 150ml Smell Attractive Perfumes P 0
37894 Sean John Unforgivable Body Mist 236ML/ 8 oz Smell Attractive Perfumes P 0
37895 Sofia Vergara 236Ml Body Mist Smell Attractive Perfumes P 0
37896 Park Avenue Fragrance Body Spray - Cool Blue 250 ml Smell Attractive Perfumes P 0
37897 Theirry Mugler Alien Man Deodorant 150Ml Smell Attractive Perfumes P 0
38150 Guerlain Oud Essentiel Edp 0.7Ml Vails Smell Attractive Perfumes P 0
38151 Jasper Conran NightshaDE EDT 1.2Ml Vails, For Men Smell Attractive Perfumes P 0
38152 Jasper Conran Nightshade EDP 1.2Ml Vails, For Women Smell Attractive Perfumes P 0
38155 L'Artisan Perfumeur Caligna EDP 1.5ml Vails, For Women Smell Attractive Perfumes P 0
38156 L'Artisan Perfumeur Explosions D'Emotions Rappelle Women EDP 1.5Ml Vails Smell Attractive Perfumes P 0