indexes and speeding up 'derived' queries

Question

I've recently noticed that a query I have is running quite slowly, at almost 1 second per query.

The query looks like this

SELECT eventdate.id, 
        eventdate.eid, 
        eventdate.date, 
        eventdate.time, 
        eventdate.title, 
        eventdate.address, 
        eventdate.rank, 
        eventdate.city, 
        eventdate.state, 
        eventdate.name, 
        source.link, 
        type, 
        eventdate.img 
FROM source 
RIGHT OUTER JOIN 
(
    SELECT event.id, 
            event.date, 
            users.name,  
            users.rank, 
            users.eid, 
            event.address, 
            event.city, 
            event.state, 
            event.lat, 
            event.`long`, 
            GROUP_CONCAT(types.type SEPARATOR ' | ') AS type 
    FROM event FORCE INDEX (latlong_idx) 
    JOIN users ON event.uid = users.id 
    JOIN types ON users.tid=types.id 
    WHERE `long` BETWEEN -74.36829174058 AND -73.64365405942 
    AND lat BETWEEN 40.35195025942 AND 41.07658794058 
    AND event.date >= '2009-10-15' 
    GROUP BY event.id, event.date
    ORDER BY event.date, users.rank DESC 
    LIMIT 0, 20 
)eventdate 
ON eventdate.uid = source.uid 
AND eventdate.date = source.date;

and the explain is

+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+-------+---------------------------------+
| id | select_type | table      | type   | possible_keys | key         | key_len | ref                          | rows  | Extra                           |
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+-------+---------------------------------+
|  1 | PRIMARY     |            | ALL    | NULL          | NULL        | NULL    | NULL                         |    20 |                                 |
|  1 | PRIMARY     | source     | ref    | iddate_idx    | iddate_idx  | 7       | eventdate.id,eventdate.date  |   156 |                                 |
|  2 | DERIVED     | event      | ALL    | latlong_idx   | NULL        | NULL    | NULL                         | 19500 | Using temporary; Using filesort |
|  2 | DERIVED     | types      | ref    | eid_idx       | eid_idx     | 4       | active.event.id              | 10674 | Using index                     |
|  2 | DERIVED     | users      | eq_ref | id_idx        | id_idx      | 4       | active.types.id              |     1 | Using where                     |
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+-------+---------------------------------+

I've tried using 'force index' on latlong, but that doesn't seem to speed things up at all.

Is it the derived table that is causing the slow responses? If so, is there a way to improve the performance of this?

--------EDIT------------- I've attempted to improve the formatting to make it more readable, as well

I run the same query changing only the 'WHERE statement as

WHERE users.id = (
SELECT users.id
FROM users
WHERE uidname = 'frankt1'
ORDER BY users.approved DESC , users.rank DESC
LIMIT 1 )
AND date & gt ; = '2009-10-15'
GROUP BY date
ORDER BY date)

That query runs in 0.006 seconds

the explain looks like

+----+-------------+------------+-------+---------------+---------------+---------+------------------------------+------+----------------+
| id | select_type | table      | type  | possible_keys | key           | key_len | ref                          | rows | Extra          |
+----+-------------+------------+-------+---------------+---------------+---------+------------------------------+------+----------------+
|  1 | PRIMARY     |            | ALL   | NULL          | NULL          | NULL    | NULL                         |   42 |                |
|  1 | PRIMARY     | source     | ref   | iddate_idx    |  iddate_idx   | 7       | eventdate.id,eventdate.date  |  156 |                |
|  2 | DERIVED     | users      | const | id_idx        |  id_idx       | 4       |                              |    1 |                |
|  2 | DERIVED     | event      | range | eiddate_idx   | eiddate_idx   | 7       | NULL                         |   24 | Using where    |
|  2 | DERIVED     | types      | ref   | eid_idx       | eid_idx       | 4       | active.event.bid             |    3 | Using index    |
|  3 | SUBQUERY    | users      | ALL   | idname_idx    | idname_idx    | 767     |                              |    5 | Using filesort |
+----+-------------+------------+-------+---------------+---------------+---------+------------------------------+------+----------------+

Answer 1

The only way to clean up that mammoth SQL statement is to go back to the drawing board and carefully work though your database design and requirements. As soon as you start joining 6 tables and using an inner select you should expect incredible execution times.

As a start, ensure that all your id fields are indexed, but better to ensure that your design is valid. I don't know where to START looking at your SQL - even after I reformatted it for you.

Note that 'using indexes' means you need to issue the correct instructions when you CREATE or ALTER the tables you are using. See for instance MySql 5.0 create indexes