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 = 'ru' 
  AND cscart_product_tabs.company_id = 1 
ORDER BY 
  position

Query time 0.00080

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

Result

tab_id tab_type block_id template addon position status is_primary product_ids company_id show_in_popup ab__smc_hide_content ab__smc_override ab__smc_height lang_code name ab__smc_show_more ab__smc_show_less type properties storefront_id
1 T 0 blocks/product_tabs/description.tpl 1 A Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Описание
2 T 0 blocks/product_tabs/features.tpl 2 A Y 38202 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Характеристики
3 T 0 blocks/product_tabs/files.tpl 3 A Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Файлы
4 T 0 addons/buy_together/blocks/product_tabs/buy_together.tpl buy_together 4 A Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Комбинации товаров
5 T 0 addons/tags/blocks/product_tabs/tags.tpl tags 5 A Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Теги
6 T 0 addons/attachments/blocks/product_tabs/attachments.tpl attachments 6 A Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Прикрепленные файлы
7 B 47 product_variations 7 A N 1 Y a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Просмотреть список опций 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
8 T 0 addons/discussion/blocks/product_tabs/discussion.tpl discussion 8 A Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Отзывы
9 T 0 addons/required_products/blocks/product_tabs/required_products.tpl required_products 9 D Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Обязательные товары
10 B 123 10 D N 38960 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Отзыв html_block a:1:{s:8:"template";s:21:"blocks/html_block.tpl";} 1
91 T 0 addons/ab__video_gallery/blocks/product_tabs/ab__video_gallery.tpl ab__video_gallery 91 D Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Видео галерея
94 T 0 addons/ab__deal_of_the_day/blocks/product_tabs/ab__deal_of_the_day.tpl ab__deal_of_the_day 94 D Y 1 N a:3:{s:5:"phone";s:1:"N";s:6:"tablet";s:1:"N";s:7:"desktop";s:1:"Y";} N 250 ru Промо-акции