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 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'tr' 
  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 (
    603, 
    749, 
    721, 
    691, 
    705, 
    655, 
    591, 
    734, 
    605, 
    611, 
    616, 
    620, 
    644, 
    722, 
    750, 
    692, 
    706, 
    656, 
    592, 
    735, 
    606, 
    612, 
    617, 
    621, 
    645, 
    723, 
    751, 
    693, 
    707, 
    657, 
    593, 
    736, 
    607, 
    613, 
    618, 
    622, 
    646, 
    724, 
    752, 
    694, 
    708, 
    647, 
    658, 
    594, 
    737, 
    608, 
    614, 
    619, 
    623, 
    725, 
    753, 
    648, 
    695, 
    709, 
    659, 
    595, 
    738, 
    609, 
    615, 
    624, 
    726, 
    739, 
    754, 
    649, 
    696, 
    710, 
    660, 
    596, 
    610, 
    625, 
    727, 
    740, 
    755, 
    650, 
    697, 
    711, 
    661, 
    597, 
    626, 
    728, 
    741, 
    756, 
    651, 
    698, 
    712, 
    662, 
    627, 
    729, 
    742, 
    757, 
    652, 
    699, 
    713, 
    663, 
    628, 
    730, 
    743, 
    758, 
    653, 
    700, 
    714, 
    664, 
    629, 
    731, 
    744, 
    759, 
    654, 
    701, 
    715, 
    555, 
    665, 
    630, 
    732, 
    745, 
    760, 
    702, 
    716, 
    556, 
    666, 
    631, 
    746, 
    761, 
    703, 
    717, 
    557, 
    667, 
    632, 
    604, 
    747, 
    762, 
    668, 
    704, 
    718, 
    558, 
    633, 
    763, 
    748, 
    669, 
    719, 
    559, 
    634, 
    764, 
    670, 
    720, 
    560, 
    635, 
    765, 
    671, 
    561, 
    636, 
    766, 
    672, 
    562, 
    637, 
    767, 
    673, 
    563, 
    638, 
    768, 
    674, 
    564, 
    639, 
    769, 
    675, 
    640, 
    770, 
    676, 
    641, 
    771, 
    677, 
    642, 
    772, 
    678, 
    643, 
    773, 
    679, 
    774, 
    680, 
    775, 
    681, 
    776, 
    682, 
    777, 
    683, 
    778, 
    684, 
    779, 
    685, 
    780, 
    686, 
    781, 
    687, 
    782, 
    688, 
    783, 
    689, 
    784, 
    690, 
    785
  ) 
  AND companies.status IN ('A') 
  AND products.company_id IN (3, 4) 
  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.product_type != 'D' 
GROUP BY 
  products.product_id 
ORDER BY 
  product asc, 
  products.product_id ASC 
LIMIT 
  0, 12

Query time 0.00584

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE products_categories index PRIMARY,pt PRIMARY 6 29 Using where; Using index; Using temporary; Using filesort
1 SIMPLE products eq_ref PRIMARY,status,idx_parent_product_id PRIMARY 3 hobilifedt.products_categories.product_id 1 Using where
1 SIMPLE descr1 eq_ref PRIMARY,product_id PRIMARY 9 hobilifedt.products_categories.product_id,const 1 Using where
1 SIMPLE prices ref usergroup,product_id,lower_limit,usergroup_id usergroup 3 hobilifedt.products_categories.product_id 1 Using where; Using index
1 SIMPLE companies eq_ref PRIMARY PRIMARY 4 hobilifedt.products.company_id 1 Using where
1 SIMPLE cscart_categories eq_ref PRIMARY,c_status,p_category_id PRIMARY 3 hobilifedt.products_categories.category_id 1 Using where

Result

product_id product company_name product_type parent_product_id
1127 SU RADYATÖRÜ BSG 17118617905 Yedek Parça Pazar P 0
1132 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] Yedek Parça Pazar P 0
1136 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] Yedek Parça Pazar P 0
1137 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] Yedek Parça Pazar P 0
1138 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] [CLONE] Yedek Parça Pazar P 0
1143 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] [CLONE] Yedek Parça Pazar P 0
1144 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] [CLONE] Yedek Parça Pazar P 0
1145 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] [CLONE] Yedek Parça Pazar P 0
1146 SU RADYATÖRÜ BSG 17118617905 [CLONE] [CLONE] [CLONE] [CLONE] Yedek Parça Pazar P 0
1128 SU RADYATÖRÜ BSG LR021778 Yedek Parça Pazar P 0
1130 SU RADYATÖRÜ BSG LR021778 [CLONE] Yedek Parça Pazar P 0
1133 SU RADYATÖRÜ BSG LR021778 [CLONE] Yedek Parça Pazar P 0