SELECT 
  * 
FROM 
  cscart_bm_locations as l 
  LEFT JOIN cscart_bm_locations_descriptions as d ON d.location_id = l.location_id 
  AND d.lang_code = 'en' 
WHERE 
  l.layout_id = 1 
  AND l.dispatch IN ('index.index') 
ORDER BY 
  l.object_ids desc 
LIMIT 
  1

Query time 0.00021

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ALL 15 Using where; Using filesort
1 SIMPLE d eq_ref PRIMARY PRIMARY 10 cscart_dev_4_16.l.location_id,const 1 Using where

Result

location_id dispatch is_default layout_id object_ids custom_html position lang_code name title meta_description meta_keywords
2 index.index 0 1 20 en Homepage Shopping Cart Software & Ecommerce Software Solutions by CS-Cart Secure and full-featured Online Shopping Cart Software with the complete set of powerful ecommerce options to create your own online store with minimum efforts involved. shopping cart, software, ecommerce software, online store