indexes and speeding up 'derived' queries
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 | +----+-------------+------------+-------+---------------+---------------+---------+------------------------------+------+----------------+
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
精彩评论