我在MySQL 5.1非规范化表上运行了这个sql查询。 它按照我想要的方式工作,但它可能很慢。我在日列上添加了一个索引,但它仍然需要更快。 关于如何更快地获得它的任何建议?(也许用加入代替?
SELECT DISTINCT(bucket) AS b,
(possible_free_slots -
(SELECT COUNT(availability)
FROM ip_bucket_list
WHERE bucket = b
AND availability = 'used'
AND tday = 'evening'
AND day LIKE '2012-12-14%'
AND network = '10_83_mh1_bucket')) AS free_slots
FROM ip_bucket_list
ORDER BY free_slots DESC;
各个查询速度很快:
SELECT DISTINCT(bucket) FROM ip_bucket_list;
1024 rows in set (0.05 sec)
SELECT COUNT(availability) from ip_bucket_list WHERE bucket = 0 AND availability = 'used' AND tday = 'evening' AND day LIKE '2012-12-14%' AND network = '10_83_mh1_bucket';
1 row in set (0.00 sec)
桌子:
mysql> describe ip_bucket_list;
+---------------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip | varchar(50) | YES | | NULL | |
| bucket | int(11) | NO | MUL | NULL | |
| availability | varchar(20) | YES | | NULL | |
| network | varchar(100) | NO | MUL | NULL | |
| possible_free_slots | int(11) | NO | | NULL | |
| tday | varchar(20) | YES | | NULL | |
| day | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
+---------------------+--------------+------+-----+-------------------+----------------+
和DESC:
DESC SELECT DISTINCT(bucket) as b,(possible_free_slots - (SELECT COUNT(availability) from ip_bucket_list WHERE bucket = b AND availability = 'used' AND tday = 'evening' AND day LIKE '2012-12-14%' AND network = '10_83_mh1_bucket')) as free_slots FROM ip_bucket_list ORDER BY free_slots DESC;
+----+--------------------+----------------+------+-----------------------------------------+--------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------+------+-----------------------------------------+--------+---------+------+--------+---------------------------------+
| 1 | PRIMARY | ip_bucket_list | ALL | NULL | NULL | NULL | NULL | 328354 | Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | ip_bucket_list | ref | bucket,network,ip_bucket_list_day_index | bucket | 4 | func | 161 | Using where |
+----+--------------------+----------------+------+-----------------------------------------+--------+---------+------+--------+---------------------------------+
我会使用连接将相关的子查询从 SELECT
子句移动到 FROM
子句中:
SELECT distinct bucket as b,
(possible_free_slots - a.avail) as free_slots
FROM ip_bucket_list ipbl left outer join
(SELECT bucket COUNT(availability) as avail
from ip_bucket_list
WHERE availability = 'used' AND tday = 'evening' AND
day LIKE '2012-12-14%' AND network = '10_83_mh1_bucket'
) on a
on ipbl.bucket = avail.bucket
ORDER BY free_slots DESC;
SELECT
子句中的版本可能会针对每一行重新运行(甚至在distinct
运行之前)。 通过将其放在from
子句中,ip_bucket_list表将仅扫描一次。
另外,如果您希望每个存储桶只显示一次,那么我建议您使用 group by
而不是 distinct
. 它将澄清查询的目的。 您可以完全消除对该表的第二个引用,如下所示:
SELECT bucket as b,
max(possible_free_slots -
(case when availability = 'used' AND tday = 'evening' AND
day LIKE '2012-12-14%' AND network = '10_83_mh1_bucket'
then 1 else 0
end)
) as free_slots
FROM ip_bucket_list
group by bucket
ORDER BY free_slots DESC;
为了加快查询版本,您需要在 bucket
上索引,因为这用于相关的子查询。
尝试将子查询移动到主查询中 - 如下所示:
SELECT b.bucket AS b,
b.possible_free_slots - COUNT(l.availability) AS free_slots
FROM ip_bucket_list b
LEFT JOIN ip_bucket_list l
ON l.bucket = b.bucket
AND l.availability = 'used'
AND l.tday = 'evening'
AND l.day LIKE '2012-12-14%'
AND l.network = '10_83_mh1_bucket'
GROUP BY b.bucket, b.possible_free_slots
ORDER BY 2 DESC