SELECT COUNT(p.id) as num 
       FROM posts p 
       INNER JOIN niche_posts np 
       ON np.posts_id = p.id AND np.niche_id = 16  
       WHERE p.city_id = 7 AND p.show = 1;

explain SELECT SQL_NO_CACHE COUNT(p.id) as num FROM posts p INNER JOIN niche_posts np ON np.posts_id = p.id AND np.niche_id = 16  WHERE p.city_id = 7 AND p.show = 1;
+----+-------------+-------+--------+---------------------------------------------+----------+---------+----------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                               | key      | key_len | ref                              | rows   | Extra       |
+----+-------------+-------+--------+---------------------------------------------+----------+---------+----------------------------------+--------+-------------+
|  1 | SIMPLE      | np    | ref    | PRIMARY,niche_id,posts_id                   | niche_id | 1       | const                            | 491200 |             |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY,city_id_source_createdon,id_city_id | PRIMARY  | 4       | db.np.posts_id |      1 | Using where |
+----+-------------+-------+--------+---------------------------------------------+----------+---------+----------------------------------+--------+-------------+
2 rows in set (0.00 sec)