Sandbox

SELECT 
  b.*, 
  d.*, 
  c.* 
FROM 
  cscart_bm_blocks as b 
  LEFT JOIN cscart_bm_blocks_descriptions as d ON b.block_id = d.block_id 
  LEFT JOIN cscart_bm_blocks_content as c ON b.block_id = c.block_id 
  AND d.lang_code = c.lang_code 
WHERE 
  b.block_id = 674 
  AND d.lang_code = 'se' 
  AND b.company_id = 2 
  AND (
    (
      c.object_id = 8365 
      AND c.object_type like 'products'
    ) 
    OR (
      c.object_id = 0 
      AND c.object_type like ''
    )
  ) 
ORDER BY 
  snapping_id DESC 
LIMIT 
  1

Query time 0.00035

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	b	const	PRIMARY	PRIMARY	4	const	1
1	SIMPLE	d	const	PRIMARY	PRIMARY	10	const,const	1
1	SIMPLE	c	ref	PRIMARY	PRIMARY	4	const	3	Using where

Result

block_id	type	properties	company_id	fpc_exclude_cache	lang_code	name	snapping_id	object_id	object_type	content
674	html_block	a:1:{s:8:"template";s:21:"blocks/html_block.tpl";}	2	N	se	Stegu designer	0	0		a:1:{s:7:"content";s:133:"<p><iframe frameborder="0" height="768" scrolling="no" src="http://config.gomega.biz/swc.php?lang=us-US" width="1024"></iframe></p> ";}