mysql - mysql 中的索引 SELECT AS 或使用 View

Question

我对一个大的 mysql 查询(mysql 5.0)感到困惑，我希望这里有人可以提供帮助。

之前我问过如何从连接查询中获取不同的值 mysql count only for distinct values in joined query

我得到的响应(使用带有 join as 的子查询)

select *from media minner join     ( select uid     from users_tbl     limit 0,30) map  on map.uid = m.uidinner join users_tbl u  on u.uid = m.uid

不幸的是，我的查询变得更加不守规矩，尽管我正在运行它，但加入派生表花费的时间太长，因为派生查询没有可用的索引。

我的查询现在看起来像这样

SELECT mdate.bid, mdate.fid, mdate.date, mdate.time, mdate.title, mdate.name,        mdate.address, mdate.rank, mdate.city, mdate.state, mdate.lat, mdate.`long`,       ext.link,        ext.source, ext.pre, meta, mdate.imgFROM extRIGHT OUTER JOIN (  SELECT media.bid,          media.date, media.time, media.title, users.name, users.img, users.rank, media.address,          media.city, media.state, media.lat, media.`long`,         GROUP_CONCAT(tags.tagname SEPARATOR ' | ') AS meta  FROM media  JOIN users ON media.bid = users.bid  LEFT JOIN tags ON users.bid=tags.bid  WHERE `long` BETWEEN -122.52224684058 AND -121.79760915942    AND lat BETWEEN 37.07500915942 AND 37.79964684058    AND date = '2009-02-23'  GROUP BY media.bid, media.date  ORDER BY media.date, users.rank DESC  LIMIT 0, 30) mdate ON (mdate.bid = ext.bid AND mdate.date = ext.date)

唷!

所以，正如你所看到的，如果我正确理解我的问题，我有两个没有索引的派生表(我不否认我可能以某种方式搞砸了 Join 语句，但我一直在搞乱不同的类型，这最终给了我我想要的结果吗)。

创建与此类似的查询的最佳方法是什么，这将使我能够利用索引？我敢说，我实际上还有一张 table 可以在以后添加到组合中。

目前，我的查询需要 0.8 秒才能完成，但我确信如果我可以利用索引，速度可能会快得多。

Answer 1

首先，检查 ext(bid, date)、users(bid) 和 tags(bid) 上的索引，你真的应该拥有它们。

不过，似乎是 LONG 和 LAT 给您带来了最多的问题。您应该尝试将 LONG 和 LAT 保留为 (坐标 POINT )，创建一个 此列上的 SPATIAL INDEX 并进行如下查询:

WHERE MBRContains(@MySquare, coordinate)

如果您由于某种原因无法更改架构，您可以尝试创建包含 date 作为第一个字段的其他索引:

CREATE INDEX ix_date_long ON media (date, `long`)
CREATE INDEX ix_date_lat ON media (date, lat)

当您将日期上的精确搜索与轴上的范围搜索结合使用时，这些索引对于您的查询将更加高效。