修改此查询以便子查询更具体的更好方法? [英] A better way to modify this query so the subquery will be more specific?

问题描述

这是我正在使用的当前查询，直到现在它都做得很好:

This is the current query that I was working with and it was doing a good job until now:

SELECT   a.*
FROM     ads AS a
WHERE    id = (
   SELECT   id
   FROM     ads AS b
   WHERE    a.zone_name=b.zone_name
      AND   publish_date <= CURDATE() AND expire_date >= CURDATE() AND active = 1
   LIMIT 1
)

它为每个现有区域返回一个广告，然后在按区域对它们进行排序后，在模板中的适当位置显示适当的广告.

It returns one ad per every existing zone, and then I display the appropriate ad in the appropriate place in my template after sorting them by zones.

现在我们引入了响应式布局，因此区域不能总是适合广告的相同尺寸，我希望始终获取适合的最大尺寸.我首先使用 Javascript 读取浏览器分辨率，将其传递回 PHP，然后 PHP 和 MySQL 可以决定适合哪些广告.

Now we have introduced a responsive layout, so the zones can't always fit the same dimension of the ad, and I would like to always fetch the biggest one that fits. I am reading the browser resolution with Javascript first, passing it back to PHP and then PHP and MySQL can decide which ads to fit.

但我不知道如何比我在下面提供的解决方案更容易地将该条件放入查询中，我想要一个更好的解决方案.由于不同的区域在相同的分辨率下适合不同的尺寸，我需要一种方法来为每个区域指定一个大小，我能想到的唯一方法是逐个获取它们，为每个区域指定所需的大小，然后对它们执行 UNION ALL.

But I don't know how to fit that condition into the query easier than the solution I have provided below, and I would like a better one. Since different zones will fit different dimensions on the same resolution, I need a way to specify a size per every zone, and the only way I can think of doing that is to fetch them one by one, specifying the desired size for each zone, and then do a UNION ALL on them.

但这似乎执行起来相当缓慢，我想知道是否有更好的方法可以简化条件并可能消除 publish_date 条件的重复，避免 UNION 因此(希望)执行得更快?

But that seems like rather sluggish to execute, and I was wondering if there is a better one that would simplify the conditions and possibly remove the repetition of the publish_date condition, avoid UNION and therefore (hopefully) execute faster?

(SELECT ads.* FROM ads WHERE zone_name = 'b' AND width=468 AND publish_date <= CURDATE() AND expire_date >= CURDATE() AND active = 1 LIMIT 1)
UNION ALL
(SELECT ads.* FROM ads WHERE zone_name = 'a1' AND width=728 AND publish_date <= CURDATE() AND expire_date >= CURDATE() AND active = 1 LIMIT 1)

...

推荐答案

这是您的查询:

SELECT   a.*
FROM     ads a
WHERE    id = (SELECT   id
               FROM     ads b
               WHERE    a.zone_name=b.zone_name AND
                        publish_date <= CURDATE() AND
                        expire_date >= CURDATE() AND
                        active = 1
              LIMIT 1
             );

您似乎并不关心您获得的广告(例如获得最新的).

You don't appear to care about which ad you get (such as getting the most recent).

一个很好的性能起点是索引:

A good place to start for performance is an index:

create index idx_ads_4 on ads(zone_name, active, expire_date, publish_date)

这是一个覆盖索引"，意思是子查询中使用的所有列都使用索引.

This is a "covering index", meaning that all the columns used in the subquery use the index.

接下来，如果区域的大小不同，则将其放入表格中并使用 join.完整的查询类似于:

Next, if you have different sizes for the zones, then put that into a table and use a join. The complete query looks something like:

select a.*
from (select 'b' as zone_name, 468 as width union all
      select 'a1', 728
     ) ZoneWidths join
     ads a
     on a.zone_name = zonewidths.zone_name and a.width = zonewidths.width
where id = (SELECT   id
            FROM     ads b
            WHERE    a.zone_name=b.zone_name AND
                     a.width = b.width AND
                     publish_date <= CURDATE() AND
                     expire_date >= CURDATE() AND
                     active = 1
            LIMIT 1
           );

这个查询的合适索引也需要width:

The appropriate index for this query also needs width:

create index idx_ads_5 on ads(zone_name, width, active, expire_date, publish_date)

这篇关于修改此查询以便子查询更具体的更好方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！