Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  IF(
    shared_descr.product_id IS NOT NULL, 
    shared_descr.product, 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) 
  LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id 
  AND shared_descr.company_id = 1 
  AND shared_descr.lang_code = 'tr' 
WHERE 
  1 
  AND companies.status IN ('A') 
  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 
GROUP BY 
  products.product_id 
ORDER BY 
  products.timestamp asc, 
  products.product_id ASC 
LIMIT 
  0, 128

Query time 0.00688

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "products.`timestamp`, products.product_id",
      "temporary_table": {
        "table": {
          "table_name": "companies",
          "access_type": "system",
          "possible_keys": ["PRIMARY"],
          "rows": 1,
          "filtered": 100
        },
        "table": {
          "table_name": "shared_descr",
          "access_type": "system",
          "possible_keys": ["PRIMARY", "product_id", "company_id"],
          "rows": 0,
          "filtered": 0,
          "const_row_not_found": true
        },
        "table": {
          "table_name": "cscart_categories",
          "access_type": "ALL",
          "possible_keys": ["PRIMARY", "c_status", "p_category_id"],
          "rows": 83,
          "filtered": 100,
          "attached_condition": "(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)"
        },
        "table": {
          "table_name": "products_categories",
          "access_type": "ref",
          "possible_keys": ["PRIMARY", "pt"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["category_id"],
          "ref": ["c.cscart_categories.category_id"],
          "rows": 2,
          "filtered": 100,
          "using_index": true
        },
        "table": {
          "table_name": "products",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "status", "idx_parent_product_id"],
          "key": "PRIMARY",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["c.products_categories.product_id"],
          "rows": 1,
          "filtered": 96.36938477,
          "attached_condition": "products.parent_product_id = 0 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` = 'A'"
        },
        "table": {
          "table_name": "descr1",
          "access_type": "eq_ref",
          "possible_keys": ["PRIMARY", "product_id"],
          "key": "PRIMARY",
          "key_length": "9",
          "used_key_parts": ["product_id", "lang_code"],
          "ref": ["c.products_categories.product_id", "const"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "trigcond(descr1.lang_code = 'tr')"
        },
        "table": {
          "table_name": "prices",
          "access_type": "ref",
          "possible_keys": [
            "usergroup",
            "product_id",
            "lower_limit",
            "usergroup_id"
          ],
          "key": "usergroup",
          "key_length": "3",
          "used_key_parts": ["product_id"],
          "ref": ["c.products_categories.product_id"],
          "rows": 2,
          "filtered": 97.23320007,
          "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)",
          "using_index": true
        }
      }
    }
  }
}

Result

product_id	product	company_name	product_type
4	40" Class (40.0" Diag.) LED 5003 Series TV	CS-Cart	P
1	65" Class (64.5" Diag.) LED 8000 Series Smart TV	CS-Cart	P
5	46" Class (45.9" Diag.) LCD 610 Series TV	CS-Cart	P
7	Trefoil Hoodie	CS-Cart	P
8	Toshiba 32TL515U 32" Class 1080P 3D LED HD TV	CS-Cart	P
9	Toshiba 42TL515U 42" Class 1080P 3D LED HD TV	CS-Cart	P
10	Toshiba 47TL515U 47" Class 1080P 3D LED HD TV	CS-Cart	P
11	Collegiate 72 Tee	CS-Cart	P
12	100g Pants	CS-Cart	P
13	Your Idea, Inc. 12 Steps to Building a Million Dollar Business -- Starting Today!	CS-Cart	P
14	Toshiba 40E220U 40" Class 1080P HD LCD TV	CS-Cart	P
15	Nike Chambray Super Runner Men's Jacket	CS-Cart	P
16	3D Blu-ray Disc™ Player (BD-D7500)	CS-Cart	P
17	101 Things Everyone Should Know About Economics A Down and Dirty Guide to Everything from Securities and Derivatives to Interest Rates and Hedge Funds—And What They Mean For You	CS-Cart	P
18	3D Blu-ray Disc™ Player (BD-D7000)	CS-Cart	P
19	Toshiba BDX2150 Blu-ray Player	CS-Cart	P
20	The ABCs of Gold Investing. How to Protect and Build Your Wealth with Gold	CS-Cart	P
21	Toshiba SD3300 DVD Player	CS-Cart	P
22	7.1 Channel Blu-ray 3D Home Theater System	CS-Cart	P
23	5.1 Channel Blu-ray 3D Home Theater System	CS-Cart	P
24	5.1 Channel Blu-ray 3D Home Theater System	CS-Cart	P
25	HW-D7000 AV Receiver with built-in Blu-ray Disc® Player	CS-Cart	P
26	Mac OS X Lion: The Missing Manual	CS-Cart	P
27	Trefoil Logo Hoodie	CS-Cart	P
28	Run Perf 7/8 Ti	CS-Cart	P
29	TomTom GO LIVE Top Gear edition	CS-Cart	P
30	GO 2535 TM WTE	CS-Cart	P
31	Nike Classic Solid Women's Jersey Shorts	CS-Cart	P
32	nüvi® 3450	CS-Cart	P
33	nüvi® 2455LT	CS-Cart	P
34	Nike "Untouchable" Women's Tank Top	CS-Cart	P
35	Managing Enterprise Content: A Unified Content Strategy, 2nd Edition	CS-Cart	P
36	Routing Protocols and Concepts: CCNA Exploration Companion Guide	CS-Cart	P
37	DPX308U	CS-Cart	P
38	Nike Futura Unravel Women's T-Shirt	CS-Cart	P
39	DEH-80PRS	CS-Cart	P
40	DEH-P8400BH	CS-Cart	P
41	Gift Set (Onesie Plus Socks)	CS-Cart	P
42	DEH-1300MP	CS-Cart	P
43	FH-P8000BT	CS-Cart	P
44	U.S. History and Government, Second Edition	CS-Cart	P
45	The Process of Research in Psychology	CS-Cart	P
46	The Official SAT Study Guide: Second Edition(TM)	CS-Cart	P
47	MARVEL® VS. CAPCOM® 3: FATE OF TWO WORLDS SPECIAL EDITION (PS3)	CS-Cart	P
48	TS-MR2040	CS-Cart	P
49	TS-MR1640	CS-Cart	P
50	TS-D1720C	CS-Cart	P
51	TS-D6902R	CS-Cart	P
52	KFC-W3013PS	CS-Cart	P
53	KFC-W3012	CS-Cart	P
54	TS-W3002D2\|D4	CS-Cart	P
55	PRS-D800	CS-Cart	P
56	GM-6500F	CS-Cart	P
57	GM-D8500M	CS-Cart	P
58	AVH-P8400BH	CS-Cart	P
59	AVH-P4400BH	CS-Cart	P
60	AVIC-Z130BT	CS-Cart	P
62	XRS 9945	CS-Cart	P
63	XRS 9770	CS-Cart	P
64	XRS 9745	CS-Cart	P
65	XRS 9370	CS-Cart	P
67	HTC Wildfire S - White (T-Mobile)	CS-Cart	P
68	Titan	CS-Cart	P
69	Rhyme	CS-Cart	P
70	EVO Design 4G	CS-Cart	P
71	DROID INCREDIBLE 2	CS-Cart	P
72	DROID RAZR	CS-Cart	P
73	DROID BIONIC	CS-Cart	P
74	Droid 3	CS-Cart	P
75	DROID PRO	CS-Cart	P
76	FLIPSIDE with MOTOBLUR	CS-Cart	P
78	adizero Rush Shoes	CS-Cart	P
79	adiZero Tempaia Shoes	CS-Cart	P
80	Nike Tenkay Slip TXTL Women's Shoe	CS-Cart	P
81	Minnetonka Original Cowhide Driving Moc	CS-Cart	P
82	Legend of the Guardians: the Owls of Ga'hoole(VG)	CS-Cart	P
83	Speed Racer: The Videogame (Wii)	CS-Cart	P
84	Lego Batman (Wii)	CS-Cart	P
85	Looney Tunes: Acme Arsenal (Wii)	CS-Cart	P
86	Nokia 700	CS-Cart	P
87	Sesame Street:Cookie's Counting Carnival (Wii)	CS-Cart	P
88	Lord of the Rings: War in the North(PS3)	CS-Cart	P
89	Green Lantern: Rise of the Manhunters (PS3)	CS-Cart	P
90	F.E.A.R. 3 (PS3) MX	CS-Cart	P
91	Montblanc Nicolas Rieussec Chronograph Automatic	CS-Cart	P
92	Montblanc TimeWalker Automatic	CS-Cart	P
93	Scene It? Bright Lights! Big Screen(X360)	CS-Cart	P
94	Batman: Arkham City (X360)	CS-Cart	P
95	Game Party: In Motion (X360)	CS-Cart	P
96	Batman: Arkham City (X360) CE	CS-Cart	P
97	Harry Potter and the Deathly Hallows - Part 2 (Blu-ray+DVD+UltraViolet Digital Copy Combo Pack)	CS-Cart	P
100	The Hangover Part II (Blu-ray)	CS-Cart	P
101	Nokia X3-02	CS-Cart	P
102	PR 100 Women's White Quartz Watch	CS-Cart	P
103	Fringe: The Complete Third Season (Blu-Ray)	CS-Cart	P
104	Final Destination 5 (Blu-ray)	CS-Cart	P
105	Nokia Lumia 800	CS-Cart	P
106	Nokia E5-00	CS-Cart	P
107	Montblanc Classic Collection Money Clip	CS-Cart	P
108	Due Date (Blu-ray)	CS-Cart	P
109	Montblanc Contemporary Collection Key Ring with Montblanc emblem	CS-Cart	P
110	Roots 30th Anniversary Special Edition (DVD)	CS-Cart	P
111	Last Samurai, The (DVD) (WS)	CS-Cart	P
112	Gone with The Wind: 2-Disc Special Edition	CS-Cart	P
113	SGH-I677	CS-Cart	P
114	Birds of Prey: The Complete Series (DVD)	CS-Cart	P
115	Evolve SST.2 X9 Complete Bike 10SPD '12	CS-Cart	P
116	I Am Legend (DVD) (WS)	CS-Cart	P
117	SCH-I515	CS-Cart	P
118	Smallville: The Complete Tenth Season	CS-Cart	P
119	The Big Bang Theory: The Complete Fourth Season	CS-Cart	P
120	Samsung Galaxy S II, Epic 4G Touch (Black)	CS-Cart	P
121	Fringe: The Complete Third Season	CS-Cart	P
122	Evolution SST.2 XO Complete Bike 10SPD12	CS-Cart	P
123	Supernatural: The Complete Sixth Season	CS-Cart	P
124	Truth SST.2 X9 Complete Bike 10SPD '12	CS-Cart	P
125	Samsung Galaxy S II	CS-Cart	P
126	Casio PRIZM fx-CG10	CS-Cart	P
127	Fore CR5 SRAM Red	CS-Cart	P
128	Casio FX-115ES	CS-Cart	P
129	Fore CR1	CS-Cart	P
130	Casio DR-210TM	CS-Cart	P
131	Catania	CS-Cart	P
132	Samsung Galaxy Player 5.0	CS-Cart	P
133	Samsung Galaxy Player 4.0	CS-Cart	P
134	Creative ZEN Style M300 MP3 Player 16GB (Black)	CS-Cart	P
135	Creative ZEN Touch 2 MP3 Player 8GB (Black)	CS-Cart	P
136	BlazBlue: Continuum Shift EXTEND	CS-Cart	P