Sandbox

SELECT 
  cscart_cp_power_seo_filter_names.variant_id, 
  cscart_cp_power_seo_filter_names.filter_id, 
  cscart_cp_power_seo_filter_names.type, 
  cscart_cp_power_seo_filter_names.name, 
  cscart_product_filters.field_type 
FROM 
  cscart_cp_power_seo_filter_names 
  LEFT JOIN cscart_product_filters ON cscart_cp_power_seo_filter_names.filter_id = cscart_product_filters.filter_id 
WHERE 
  (name = 'bmw-f11')

Query time 0.00022

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	cscart_cp_power_seo_filter_names	ref	PRIMARY,name	PRIMARY	752	const	1	Using where; Using index
1	SIMPLE	cscart_product_filters	eq_ref	PRIMARY	PRIMARY	3	hobilifedt.cscart_cp_power_seo_filter_names.filter_id	1	Using where

Result

variant_id	filter_id	type	name	field_type
1217	315	v	bmw-f11