MYSQL optimization for Using temporary; Using filesort!!! -> HELP

Question

Guys I'm trying for some time now and I can't manage to optimize the Using temporary; Using filesort from my EXPLAIN query. OK so, I have this query:

 SELECT a.adid, a.adtitle, a.addesc, scat.subcatname
FROM ads a
INNER JOIN cities ct ON a.cityid = ct.cityid
INNER JOIN subcats scat ON a.subcatid = scat.subcatid
INNER JOIN cats cat ON scat.catid = cat.catid
WHERE a.enabled = '1'
AND a.verified ='1'
GROUP BY a.adid
ORDER BY a.createdon DESC
LIMIT 16;

When I do EXPLAIN ... I get this:

    EXPLAIN SELECT a.adid, a.adtitle, a.addesc, scat.subcatname 
FROM ads a 
INNER JOIN cities ct ON a.cityid = ct.cityid 
INNER JOIN subcats scat ON a.subcatid = scat.subcatid 
INNER JOIN cats cat ON scat.catid = cat.catid 
WHERE a.enabled = '1' 
AND a.verified ='1' 
GROUP BY a.adid 
ORDER BY a.createdon DESC LIMIT 16;

+----+-------------+-------+--------+----------------------------------+----------+---------+--------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys                    | key      | key_len | ref                            | rows | Extra                           |
+----+-------------+-------+--------+----------------------------------+----------+---------+--------------------------------+------+---------------------------------+
|  1 | SIMPLE      | cat   | system | PRIMARY                          | NULL     | NULL    | NULL                           |    1 | Using temporary; Using filesort | 
|  1 | SIMPLE      | scat  | ref    | PRIMARY,catid                    | catid    | 2       | const                          |    7 |                                 | 
|  1 | SIMPLE      | a     | ref    | subcatid,cityid,verified,enabled | subcatid | 2       | bakecai_incontri.scat.subcatid |  954 | Using where                     | 
|  1 | SIMPLE      | ct    | eq_ref | PRIMARY                          | PRIMARY  | 2       | bakecai_incontri.a.cityid      |    1 | Using index                     | 
+----+-------------+-------+--------+----------------------------------+----------+---------+--------------------------------+------+---------------------------------+
4 rows in set (0.00 sec)

I have tried some KEY's but no result: for example I create an index for createdon and adid but mysql won't use it.

Here are the keys for my table:

SHOW INDEX FROM ads;

+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|Table| Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ads |          0 | PRIMARY            |            1 | adid        | A         |        8592 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | subcatid           |            1 | subcatid    | A         |           9 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | cityid             |            1 | cityid      | A         |         103 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | verified           |            1 | verified    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| ads |          1 | enabled            |            1 | enabled     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| ads |          1 | idx_createdon_adid |            1 | createdon   | A         |        8592 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | idx_createdon_adid |            2 | adid        | A         |        8592 |     NULL | NULL   |      | BTREE      |         | 
| ads |          1 | srch_text          |            1 | adtitle     | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         | 
| ads |          1 | srch_text          |            2 | addesc      | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         | 
| ads |          1 | srch_text          |            3 | nome        | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         | 
+-------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.00 sec)

HERE are some brief description of the tables:

 DESCRIBE ads
    -> ;
+-----------+----------------------+------+-----+---------------------+----------------+
| Field     | Type                 | Null | Key | Default             | Extra          |
+-----------+----------------------+------+-----+---------------------+----------------+
| adid      | int(10) unsigned     | NO   | PRI | NULL                | auto_increment | 
| nome      | varchar(255)         | YES  |     | NULL                |                | 
| eta       | varchar(255)         | YES  |     | NULL                |                | 
| adtitle   | varchar(100)         | YES  | MUL | NULL                |                | 
| addesc    | text                 | YES  |     | NULL                |                | 
| email     | varchar(50)          | YES  |     | NULL                |                | 
| phone     | varchar(255)         | YES  |     | NULL                |                | 
| showemail | enum('0','1','2')    | YES  |     | NULL                |                | 
| code      | varchar(35)          | YES  |     | NULL                |                | 
| first     | varchar(255)         | YES  |     | NULL                |                | 
| cityid    | smallint(5) unsigned | NO   | MUL | 0                   |                | 
| subcatid  | smallint(5) unsigned | NO   | MUL | 0                   |                | 
| price     | decimal(10,2)        | NO   |     | 0.00                |                | 
| hits      | int(10) unsigned     | NO   |     | 0                   |                | 
| ip        | varchar(15)          | YES  |     | NULL                |                | 
| link      | varchar(255)         | YES  |     | http://             |                | 
| verified  | enum('0','1')        | YES  | MUL | NULL                |                | 
| abused    | int(10) unsigned     | NO   |     | 0                   |                | 
| enabled   | enum('0','1')        | YES  | MUL | NULL                |                | 
| createdon | datetime             | NO   | MUL | 0000-00-00 00:00:00 |                | 
| expireson | datetime             | NO   |     | 0000-00-00 00:00:00 |                | 
| timestamp | timestamp            | NO   |     | CURRENT_TIMESTAMP   |                | 
+-----------+----------------------+------+-----+---------------------+----------------+
22 rows in set (0.02 sec)

DESCRIBE cities
    -> ;
+-----------+----------------------+------+-----+-------------------+----------------+
| Field     | Type                 | Null | Key | Default           | Extra          |
+-----------+----------------------+------+-----+-------------------+----------------+
| cityid    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment | 
| cityname  | varchar(50)          | YES  |     | NULL              |                | 
| regionid  | smallint(5) unsigned | NO   | MUL | 0                 |                | 
| pos       | smallint(5) unsigned | NO   | MUL | 0                 |                | 
| enabled   | enum('0','1')        | YES  | MUL | NULL              |                | 
| timestamp | timestamp            | NO   |     | CURRENT_TIMESTAMP |                | 
+-----------+----------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

DESCRIBE cats
    -> ;
+-----------+----------------------+------+-----+-------------------+----------------+
| Field     | Type                 | Null | Key | Default           | Extra          |
+-----------+----------------------+------+-----+-------------------+----------------+
| catid     | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment | 
| catname   | varchar(50)          | YES  |     | NULL              |                | 
| pos       | smallint(5) unsigned | NO   |     | 0                 |                | 
| enabled   | enum('0','1')        | YES  | MUL | NULL              |                | 
| timestamp | timestamp            | NO   |     | CURRENT_TIMESTAMP |                | 
+-----------+----------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)

Any help , i appreciate!

Answer 1

SELECT  a.adid, a.adtitle, a.addesc, scat.subcatname
FROM    ads a
INNER JOIN
        subcats scat
ON      a.subcatid = scat.subcatid
WHERE   a.enabled = '1'
        AND a.verified ='1'
ORDER BY a.createdon
        DESC
LIMIT 16

Removed unneeded joins and got rid of unneeded GROUP BY.

To get rid of filesort, create the following index:

CREATE INDEX ix_ads_e_v_created ON ads (enabled, verified, createdon)