索引并加速“派生”；查询

Question

我最近注意到我的一个查询运行速度非常慢，每个查询几乎需要 1 秒。

查询看起来像这样

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;

，解释是

+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+-------+---------------------------------+
| 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                     |
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+-------+---------------------------------+

我尝试在经纬度上使用“强制索引”，但这似乎根本没有加快速度。

是派生表导致响应缓慢吗？如果是这样，有没有办法提高它的性能？

- - - - 编辑 - - - - - - - 我尝试改进格式以使其更具可读性，并且

我运行相同的查询，仅更改 'WHERE 语句，因为

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)

该查询在 0.006 秒内运行，

解释如下

+----+-------------+------------+-------+---------------+---------------+---------+------------------------------+------+----------------+
| 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

清理庞大 SQL 语句的唯一方法是回到绘图板并仔细研究数据库设计和需求。一旦您开始连接 6 个表并使用内部选择，您就应该期待令人难以置信的执行时间。

首先，确保所有 id 字段都已建立索引，但最好确保您的设计有效。我不知道从哪里开始查看你的 SQL - 即使我为你重新格式化了它。

请注意，“使用索引”意味着您在创建或更改正在使用的表时需要发出正确的指令。例如，请参见 MySql 5.0 创建索引