php - Mysql 查询执行时间需要秒才能得到结果？

Question

我有两个具有以下结构的表

--

--表visitors

的表结构

CREATE TABLE IF NOT EXISTS `visitors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a_id` int(11) DEFAULT NULL,
  `visited_by` int(11) DEFAULT '0',
  `ip` varchar(30) DEFAULT NULL,
  `browser_name` varchar(255) DEFAULT NULL,
  `browser_short_name` varchar(20) DEFAULT NULL,
  `browser_version` varchar(20) DEFAULT NULL,
  `os_platform` varchar(20) DEFAULT NULL,
  `visited_time` datetime DEFAULT NULL,
  `is_visited` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

--表数字的表结构

CREATE TABLE IF NOT EXISTS `numbers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 

table 位访客总数为172,153， table 数总数为5,896

我正在尝试使用以下查询获取过去 30 天的记录

select  
x.ts AS timestamp,
COUNT( y.`id`) as no_of_visitors,
DATE( y.`visited_time`) as visited_date,
MONTH( y.`visited_time`) as month_visit,
MONTHNAME( y.`visited_time`) as visit_month_name,
WEEKOFYEAR( y.`visited_time`) as visit_week_no,
YEAR( y.`visited_time`) as year_of_visit
from 
  (SELECT  date(DATE_ADD( CURDATE( ) , INTERVAL CAST(n.id as SIGNED) - 30 DAY)) AS ts
                 FROM numbers n
                WHERE DATE_ADD(CURDATE( ), INTERVAL CAST(n.id as SIGNED) - 30 DAY) <= CURDATE( )) x 
LEFT JOIN  
visitors y 
ON  
date(y.`visited_time`) = x.ts 
GROUP BY DATE(x.ts) 
order by DATE( x.ts) desc

在本地主机中执行需要 4.7833 秒。我在查询中做错了什么？我如何尽可能快地执行查询？敬请指教

Answer 1

你不只是想要:

select
COUNT( y.`id`) as no_of_visitors,
DATE( y.`visited_time`) as visited_date,
MONTH( y.`visited_time`) as month_visit,
MONTHNAME( y.`visited_time`) as visit_month_name,
WEEKOFYEAR( y.`visited_time`) as visit_week_no,
YEAR( y.`visited_time`) as year_of_visit
from 
visitors y 
where y.`visited_time` > date_add(curdate(), interval -30 day)
group by DATE(y.`visited_time`) 
order by DATE(y.`visited_time`) desc

数字表的整个过程会导致非常大的连接操作，我认为这是低效且不必要的，尽管我真的不明白你对表数字做了什么......

编辑:如果您想要所有日期，我将在上述查询完成后加入:

select * from
all_dates a left join 
(select
COUNT( y.`id`) as no_of_visitors,
DATE( y.`visited_time`) as visited_date,
MONTH( y.`visited_time`) as month_visit,
MONTHNAME( y.`visited_time`) as visit_month_name,
WEEKOFYEAR( y.`visited_time`) as visit_week_no,
YEAR( y.`visited_time`) as year_of_visit
from 
visitors y 
where y.`visited_time` > date_add(curdate(), interval -30 day)
group by DATE(y.`visited_time`) 
order by DATE(y.`visited_time`) desc) b
on a.date = b.visited_date;

显然 all_dates 是您制作的日期表。