Sandbox

SELECT 
  *, 
  cscart_product_tabs.status as status, 
  cscart_product_tabs.block_id as block_id, 
  cscart_product_tabs.company_id as company_id, 
  cscart_product_tabs_descriptions.name as name 
FROM 
  cscart_product_tabs 
  LEFT JOIN cscart_product_tabs_descriptions ON cscart_product_tabs.tab_id = cscart_product_tabs_descriptions.tab_id 
  LEFT JOIN cscart_bm_blocks ON cscart_bm_blocks.block_id = cscart_product_tabs.block_id 
WHERE 
  cscart_product_tabs_descriptions.lang_code = 'tr' 
ORDER BY 
  position

Query time 0.00151

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	cscart_product_tabs_descriptions	ALL	PRIMARY				11	Using where; Using temporary; Using filesort
1	SIMPLE	cscart_product_tabs	eq_ref	PRIMARY	PRIMARY	3	hobilifedt.cscart_product_tabs_descriptions.tab_id	1
1	SIMPLE	cscart_bm_blocks	eq_ref	PRIMARY	PRIMARY	4	hobilifedt.cscart_product_tabs.block_id	1	Using where

Result

tab_id	tab_type	block_id	template	addon	position	status	is_primary	show_in_popup	lang_code	name	type	properties	storefront_id
1	T	0	blocks/product_tabs/description.tpl		1	A	Y	N	tr	Açıklama
2	T	0	blocks/product_tabs/features.tpl		2	A	Y	N	tr	Özellikler
3	T	0	blocks/product_tabs/files.tpl		3	A	Y	N	tr	Dosyalar
4	T	0	addons/tags/blocks/product_tabs/tags.tpl	tags	4	A	Y	N	tr	Etiketler
5	T	0	addons/attachments/blocks/product_tabs/attachments.tpl	attachments	5	D	Y	N	tr	Eklentiler
6	B	62		product_variations	6	A	N	Y	tr	Tüm varyasyonları liste halinde görüntüle	products	a:5:{s:8:"template";s:61:"addons/product_variations/blocks/products/variations_list.tpl";s:46:"product_variations.hide_add_to_wishlist_button";s:1:"N";s:23:"hide_add_to_cart_button";s:1:"N";s:36:"product_variations.show_product_code";s:1:"Y";s:44:"product_variations.show_variation_thumbnails";s:1:"Y";}	1
7	T	0	addons/product_bundles/blocks/product_tabs/product_bundles.tpl	product_bundles	7	A	Y	N	tr	Ürün paketleri
8	T	0	addons/product_reviews/blocks/product_tabs/product_reviews.tpl	product_reviews	8	A	Y	N	tr	Değerlelendirmeler
9	T	0	addons/discussion/blocks/product_tabs/discussion.tpl	discussion	9	A	Y	N	tr	Değerlelendirmeler
10	T	0	addons/required_products/blocks/product_tabs/required_products.tpl	required_products	10	A	Y	N	tr	Gerekli ürünler
11	T	0	addons/wk_iyzico_marketplace/blocks/product_tabs/installments.tpl	wk_iyzico_marketplace	11	D	Y	N	tr	Installments