描述有点复杂,我把结构照抄下来好了
第一个表:
CREATE TABLE `report` (
`date` smallint(5) unsigned NOT NULL DEFAULT '0',
`time` tinyint(3) unsigned NOT NULL DEFAULT '0',
`cgi` smallint(5) unsigned NOT NULL DEFAULT '0',
`data` varchar(2) DEFAULT NULL,
`name` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`cgi`,`date`,`time`),
KEY `date` (`date`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
第二个表:
CREATE TABLE `task` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(255) DEFAULT NULL,
`starttime` int(11) unsigned NOT NULL DEFAULT '0',
`endtime` int(11) unsigned NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=940 DEFAULT CHARSET=utf8;
函数:
CREATE DEFINER=`root`@`localhost` FUNCTION `em_func_getcgitime`(starttime datetime, endtime datetime) RETURNS datetime
BEGIN
SET @TIME_OFFSET = '-10:0:0';
IF endtime IS NULL THEN
SET endtime = NOW();
END IF;
IF timestampdiff(HOUR, starttime, endtime) < 0 THEN
RETURN 0;
ELSEIF timestampdiff(HOUR, starttime, endtime) > 24 THEN
RETURN DATE_ADD(endtime, INTERVAL @TIME_OFFSET DAY_SECOND);
ELSE
RETURN DATE_ADD(starttime, INTERVAL FLOOR(HOUR(TIMEDIFF(starttime,endtime)) / 4 * 3) HOUR);
END IF;
END;查询语句:
explain(
SELECT
t.id AS taskid, r.cgi
FROM
report r
INNER JOIN task t
WHERE
r.date = date_format(DATE(em_func_getcgitime(t.starttime, t.endtime)), '%Y-%m-%d')
AND r.time = HOUR(em_func_getcgitime(t.starttime, t.endtime)) AND r.name LIKE CONCAT('%', t.name, '%')
GROUP BY
t.id, r.cgi
)
情况是这样的,原来我在一个unix上的mysql3.X的数据库上运行的挺好的一个程序,移植到windows下mysql5.x之后发现突然速度变得无法忍受,explain了一下发现有一句查询语句没法使用索引(应该使用date索引的),仔细比较过,似乎除了数据库版本之外所有地方都是一样的,无奈了
以上的表结构是能够呈现问题的最简结构,有没有高手帮忙瞧瞧啊,问题可能是出在select当中调用了自定义的函数,但原来unix上确实是可以用的呀
第一个表:
CREATE TABLE `report` (
`date` smallint(5) unsigned NOT NULL DEFAULT '0',
`time` tinyint(3) unsigned NOT NULL DEFAULT '0',
`cgi` smallint(5) unsigned NOT NULL DEFAULT '0',
`data` varchar(2) DEFAULT NULL,
`name` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`cgi`,`date`,`time`),
KEY `date` (`date`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
第二个表:
CREATE TABLE `task` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(255) DEFAULT NULL,
`starttime` int(11) unsigned NOT NULL DEFAULT '0',
`endtime` int(11) unsigned NOT NULL DEFAULT '0',
`name` varchar(10) NOT NULL DEFAULT '',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=940 DEFAULT CHARSET=utf8;
函数:
CREATE DEFINER=`root`@`localhost` FUNCTION `em_func_getcgitime`(starttime datetime, endtime datetime) RETURNS datetime
BEGIN
SET @TIME_OFFSET = '-10:0:0';
IF endtime IS NULL THEN
SET endtime = NOW();
END IF;
IF timestampdiff(HOUR, starttime, endtime) < 0 THEN
RETURN 0;
ELSEIF timestampdiff(HOUR, starttime, endtime) > 24 THEN
RETURN DATE_ADD(endtime, INTERVAL @TIME_OFFSET DAY_SECOND);
ELSE
RETURN DATE_ADD(starttime, INTERVAL FLOOR(HOUR(TIMEDIFF(starttime,endtime)) / 4 * 3) HOUR);
END IF;
END;查询语句:
explain(
SELECT
t.id AS taskid, r.cgi
FROM
report r
INNER JOIN task t
WHERE
r.date = date_format(DATE(em_func_getcgitime(t.starttime, t.endtime)), '%Y-%m-%d')
AND r.time = HOUR(em_func_getcgitime(t.starttime, t.endtime)) AND r.name LIKE CONCAT('%', t.name, '%')
GROUP BY
t.id, r.cgi
)
情况是这样的,原来我在一个unix上的mysql3.X的数据库上运行的挺好的一个程序,移植到windows下mysql5.x之后发现突然速度变得无法忍受,explain了一下发现有一句查询语句没法使用索引(应该使用date索引的),仔细比较过,似乎除了数据库版本之外所有地方都是一样的,无奈了
以上的表结构是能够呈现问题的最简结构,有没有高手帮忙瞧瞧啊,问题可能是出在select当中调用了自定义的函数,但原来unix上确实是可以用的呀
在2个字段上建立索引,r.name LIKE CONCAT('%', t.name, '%')这种是用不到索引的
"report",0,"PRIMARY",1,"cgi","A",951,NULL,NULL,"","BTREE",""
"report",0,"PRIMARY",2,"date","A",951,NULL,NULL,"","BTREE",""
"report",0,"PRIMARY",3,"time","A",951,NULL,NULL,"","BTREE",""
"report",1,"date",1,"date","A",951,NULL,NULL,"","BTREE",""
"report",1,"date",2,"time","A",951,NULL,NULL,"","BTREE",""task:
"task",0,"PRIMARY",1,"Id","A",775,NULL,NULL,"","BTREE",""explain:
1,"SIMPLE","t","ALL",NULL,NULL,NULL,NULL,775,"Using temporary; Using filesort"
1,"SIMPLE","r","ALL",NULL,NULL,NULL,NULL,951,"Using where; Using join buffer"原查询语句将r.date = date_format(DATE(em_func_getcgitime(t.starttime, t.endtime)), '%Y-%m-%d')改成r.date = 826(随机数字里找了一个存在的)后explain:
1,"SIMPLE","r","ref","date","date","2","const",3,"Using temporary; Using filesort"
1,"SIMPLE","t","ALL",NULL,NULL,NULL,NULL,775,"Using where; Using join buffer"原查询语句将AND r.name LIKE CONCAT('%', t.name, '%')删除后explain:
1,"SIMPLE","t","ALL",NULL,NULL,NULL,NULL,775,"Using temporary; Using filesort"
1,"SIMPLE","r","index",NULL,"date","3",NULL,951,"Using where; Using index; Using join buffer"
id select_type table type possible_keys key key_len ref rows extra
force index .