title: Best optimization for MySQL query with many JOINS
body: <p>I developing a classifieds website with around 200k ads.
  When the user visit an specific category I run this query, many many times.</p>
  
  <pre><code># Time: 171129 21:04:05
  # User@Host: erich33_bache4[erich33_bache4] @ localhost []
  # Thread_id: 11596  Schema: erich33_bache3  QC_hit: No
  # Query_time: 0.629711  Lock_time: 0.000252  Rows_sent: 75  Rows_examined: 12717
  use erich33_bache3;
  SET timestamp=1512007445;
  SELECT `category`.`id` as `category_id`, `category`.`subcat` as `category_name`, `category`.`desc` as `category_desc`, `category`.`name` as `category_pretty_name`, `ads`.`id`, `ads`.`header`, `ads`.`price`, `ads`.`oldprice`, `ads`.`sellfast`, `ads`.`hash`, `ads`.`foto1`, `ads`.`foto2`, `ads`.`foto3`, `ads`.`foto4`, `ads`.`foto5`, `ads`.`user_id`, SUBSTR(ads.body, 1, 160) as body, `ads`.`subcat_id`, `ads`.`updated`, `ads`.`created`, `ads`.`email`, `ads`.`name`, `ads`.`phone`, `ads`.`hits`, `ads`.`hidden`, `promo`.`promotype` as `promo_type`, `provinces`.`prov_name`, `supercategory`.`name` as `supercategory_name`, `supercategory`.`id` as `supercategory_id`
  FROM `ads`
  JOIN `category` ON `category`.`id` = `ads`.`subcat_id`
  JOIN `supercategory` ON `supercategory`.`id` = `category`.`cat`
  JOIN `provinces` ON `provinces`.`id` = `ads`.`province_id`
  LEFT OUTER JOIN `promo` ON `promo`.`ads_id` = `ads`.`id`
  WHERE `ads`.`subcat_id` = '54'
  AND `recycle_bin` != 1
  AND `hidden` =0
  ORDER BY `promo_type` DESC, `updated` DESC
  LIMIT 75;
  </code></pre>
  
  <p>My question is, as a NO expertise in MySQL, How do I optimize the foreign tables for a Better <code>Query_time: 0.629711</code></p>
  
  <p>This is the EXPLAIN result. I'm new and noob to MySQL But I thinks, there could be some optimizations.</p>
  
  <p><a href="https://i.stack.imgur.com/XirxE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XirxE.png" alt="EXPLAIN QUERY"></a></p>