mysql 中的索引 SELECT AS 或使用视图

发布于 2024-07-14 13:36:13 字数 1625 浏览 7 评论 0原文

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

早些时候我问过如何从连接查询中获取不同的值仅对连接查询中的不同值进行计数

select *
from media m
inner join
     ( select uid
     from users_tbl
     limit 0,30) map
  on map.uid = m.uid
inner 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.img
FROM ext
RIGHT 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 语句，但我一直在搞乱不同的类型，这结束了吗？给我我想要的结果）。

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

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

原文

I'm in over my head with a big mysql query (mysql 5.0), and i'm hoping somebody here can help.

Earlier I asked how to get distinct values from a joined query
mysql count only for distinct values in joined query

The response I got worked (using a subquery with join as)

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

unfortunately, my query has grown more unruly, and though I have it running, joining into a derived table is taking too long because there is no indexes available to the derived query.

my query now looks like this

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.img
FROM ext
RIGHT 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)

phew!

SO, as you can see, if I understand my problem correctly, i have two derivative tables without indexes (and i don't deny that I may have screwed up the Join statements somehow, but I kept messing with different types, is this ended up giving me the result I wanted).

What's the best way to create a query similar to this which will allow me to take advantage of the indexes?
Dare I say, I actually have one more table to add into the mix at a later date.

Currently, my query is taking .8 seconds to complete, but I'm sure if I could take advantage of the indexes, this could be significantly faster.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

所有深爱都是秘密 2024-07-21 13:36:13

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

不过，造成大多数问题的似乎是 LONG 和 LAT。您应该尝试将 LONG 和 LAT 保留为 ( 坐标 POINT)，创建一个空间索引< /code> 在此列上并进行如下查询：

WHERE MBRContains(@MySquare, coordinate)

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

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

这些索引会更有效当您对日期使用精确搜索并结合对轴进行范围搜索时，您可以进行查询。

First, check for indices on ext(bid, date), users(bid) and tags(bid), you should really have them.

It seems, though, that it's LONG and LAT that cause you most problems. You should try keeping your LONG and LAT as a (coordinate POINT), create a SPATIAL INDEX on this column and query like that:

WHERE MBRContains(@MySquare, coordinate)

If you can't change your schema for some reason, you can try creating additional indices that include date as a first field:

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

These indices will be more efficient for you query, as you use exact search on date combined with a ranged search on axes.

回复收藏 0 原文

只是在用心讲痛 2024-07-21 13:36:13

重新开始：

问题 - 为什么要同时按 media.bid 和 media.date 进行分组？一项出价可以有多个日期的记录吗？

这是一个更简单的版本可以尝试：

<前><代码>选择
mdate.bid,
mdate.fid,
mdate.日期，
mdate.时间，
mdate.标题，
mdate.name,
mdate.地址，
mdate.rank,
mdate.城市，
mdate.状态，
mdate.lat，
mdate.`长`,
外部链接，
外部源，
分机前，
元，
mdate.img,
( SELECT GROUP_CONCAT(tags.tagname SEPARATOR ' | ')
来自标签
WHERE ext.bid = 标签.bid
ORDER BY 标签.bid GROUP BY 标签.bid
) AS 元
从
分机
左连接
媒体 ON ext.bid = media.bid AND ext.date = media.date
加入
用户ON ext.bid = users.bid
在哪里
-122.52224684058 和 -121.79760915942 之间的“长”
37.07500915942 和 37.79964684058 之间的纬度
AND ext.date = '2009-02-23'
AND users.userid IN
(
从用户中选择用户 ID 按排名 DESC LIMIT 30 排序
）
订购依据
媒体日期，
用户.rank DESC
限制 0, 30

Starting fresh:

Question - why are you grouping by both media.bid and media.date? Can a bid have records for more than one date?

Here's a simpler version to try:

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.img,
    (   SELECT GROUP_CONCAT(tags.tagname SEPARATOR ' | ')
        FROM tags 
        WHERE ext.bid = tags.bid
        ORDER BY tags.bid GROUP BY tags.bid
    ) AS meta

FROM 
    ext

LEFT JOIN
    media ON ext.bid = media.bid AND ext.date = media.date

JOIN
    users ON ext.bid = users.bid

WHERE 
    `long` BETWEEN -122.52224684058 AND -121.79760915942
    AND lat BETWEEN 37.07500915942 AND 37.79964684058
    AND ext.date = '2009-02-23'
    AND users.userid IN
    (    
        SELECT userid FROM users ORDER BY rank DESC LIMIT 30
    )

ORDER BY 
    media.date, 
    users.rank DESC
    LIMIT 0, 30

回复收藏 0 原文