Sql query optimization and debug

Question

I am trying to optimize sql query in mysql db. Tried different ways of rewriting it , adding/removing indexes, but nothing seems to decrease the load. Maybe I am missing something. Query:

select co.country_name as state, ci.city_name as city, ci.city_id, ci.country_id,
                        count(l.id) as num  
                        FROM cities ci 
                        INNER JOIN countries co ON (ci.country_id = co.country_id)
                        INNER JOIN dancers l ON (l.city_id = ci.city_id AND l.closed = 0 AND l.approved = 1 )
                        WHERE 1 AND ci.main=1                   
                        GROUP BY ci.city_id
                        ORDER BY city 

Duration : 2.01sec - 2.20sec Optimized query:

    select co.country_name as state, ci.city_name as city, ci.city_id, ci.country_id, count(l.id) as num from 
(select ci1.city_name, ci1.city_id, ci1.country_id from cities ci1 
where ci1.main=1) as ci 
INNER JOIN countries co ON (ci.country_id = co.country_id) 
INNER JOIN dancers l ON (l.city_id = ci.city_id AND l.closed = 0 AND l.approved = 1 ) GROUP BY ci.city_id ORDER BY city

Duration : 0.82sec - 0.90sec

But i feel that this query can be optimized even more but not getting the ideea how to optimized it. There are 3 tables

Table 1 : countries ( country_id, country_name)
Table 2 : cities ( city_id, city_name, main, country_id)
Table 3 : dancers ( id, country_id, city_id, closed, approved) 

I am trying to get all the cities which have main=1 and for each to count all the profiles that are into those cities joining with countries to get the country_name.

Any ideas are welcomed, thank you.

Later edit : - first query explain

+----+-------------+-------+-------------+---------------------------------------------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
| id | select_type | table |    type     |                            possible_keys                            |       key       | key_len |       ref        | rows  |                                     Extra                                      |
+----+-------------+-------+-------------+---------------------------------------------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
|  1 | SIMPLE      | l     | index_merge | city_id,closed,approved,city_id_2                                   | closed,approved |     1,2 | NULL             | 75340 | Using intersect(closed,approved); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ci    | eq_ref      | PRIMARY,state_id_2,state_id,city_name,lat,city_name_shorter,city_id | PRIMARY         |       4 | db.l.city_id     |     1 | Using where                                                                    |
|  1 | SIMPLE      | co    | eq_ref      | PRIMARY                                                             | PRIMARY         |       4 | db.ci.country_id |     1 | Using where                                                                    |
+----+-------------+-------+-------------+---------------------------------------------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+

Second query explain :

+----+-------------+------------+------+-----------------------------------+-------------+---------+------------------+-------+------------------------------------+
| id | select_type |   table    | type |           possible_keys           |     key     | key_len |       ref        | rows  |               Extra                |
+----+-------------+------------+------+-----------------------------------+-------------+---------+------------------+-------+------------------------------------+
|  1 | PRIMARY     | co         | ALL  | PRIMARY                           | NULL        | NULL    | NULL             |    51 | Using temporary; Using filesort    |
|  1 | PRIMARY     | <derived2> | ref  | <auto_key1>                       | <auto_key1> | 4       | db.co.country_id |   176 | Using where                        |
|  1 | PRIMARY     | l          | ref  | city_id,closed,approved,city_id_2 | city_id_2   | 4       | ci.city_id       |    44 | Using index condition; Using where |
|  2 | DERIVED     | ci1        | ALL  | NULL                              | NULL        | NULL    | NULL             | 11765 | Using where                        |
+----+-------------+------------+------+-----------------------------------+-------------+---------+------------------+-------+------------------------------------+

@used_by_already query explain :

+----+-------------+------------+-------------+-----------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
| id | select_type |   table    |    type     |           possible_keys           |       key       | key_len |       ref        | rows  |                                     Extra                                      |
+----+-------------+------------+-------------+-----------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+
|  1 | PRIMARY     | co         | ALL         | PRIMARY                           | NULL            | NULL    | NULL             |    51 | NULL                                                                           |
|  1 | PRIMARY     | <derived2> | ref         | <auto_key0>                       | <auto_key0>     | 4       | db.co.country_id |   565 | Using where                                                                    |
|  2 | DERIVED     | l          | index_merge | city_id,closed,approved,city_id_2 | closed,approved | 1,2     | NULL             | 75341 | Using intersect(closed,approved); Using where; Using temporary; Using filesort |
|  2 | DERIVED     | ci1        | eq_ref      | PRIMARY,state_id_2,city_id        | PRIMARY         | 4       | db.l.city_id     |     1 | Using where                                                                    |
+----+-------------+------------+-------------+-----------------------------------+-----------------+---------+------------------+-------+--------------------------------------------------------------------------------+

Answer 1

I suggest you try this:

SELECT
      co.country_name AS state
    , ci.city_name    AS city
    , ci.city_id
    , ci.country_id
    , ci.num
FROM (
      SELECT
            ci1.city_id
          , ci1.city_name
          , ci1.country_id
          , COUNT(l.id) AS num
      FROM cities ci1
      INNER JOIN dancers l ON l.city_id = ci1.city_id
            AND l.closed = 0
            AND l.approved = 1
      WHERE ci1.main = 1
      GROUP BY
            ci1.city_id
          , ci1.city_name
          , ci1.country_id
      ) AS ci
INNER JOIN countries co ON ci.country_id = co.country_id
;

And that you provide the explain plan output for further analysis if needed. When optimizing knowing what indexes exist, and having he explain plan, are essentials.

Not also, that MySQL does permit non-standard GROUP BY syntax (where only one or some of the columns in the select list are included under the group by clause). In recent versions of MySQL the default behaviour for GROUP BY has changed to SQL standard syntax (where all "non-aggregating" columns in the select list must be included under the group by clause). While your existing queries use the non-standard group by syntax, the query supplied here dis compliant.