I need to pump up my query a bit for it's taking way too long on a large DB.
我需要对我的查询进行一点改进,因为它在大型DB上花费的时间太长了。
I have the following tables
我有以下几张桌子
vb_user
+++++++++++++++++++++++++++++++++
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
++ userid ++ username ++ posts ++
++ userid + username + post ++ ++
+++++++++++++++++++++++++++++++++
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
vb_post
++++++++++++++++++++++++
+ + + + + + + + + + + + + + + + + + + + + + + +
++ userid ++ dateline ++
+ userid + dateline ++ +
++++++++++++++++++++++++
+ + + + + + + + + + + + + + + + + + + + + + + +
I use this query
我用这个查询
SELECT VBU.userid AS USER_ID
, VBU.username AS USER_NAME
, COUNT(VBP.userid) AS NUMBER_OF_POSTS_FOR_30_DAYS
, FROM_UNIXTIME(VBU.joindate) as JOIN_DATE
FROM vb_user AS VBU
LEFT JOIN vb_post AS VBP
ON VBP.userid = VBU.userid
WHERE VBU.joindate BETWEEN '__START_DATE__' AND '__END_DATE__'
AND VBP.dateline BETWEEN VBU.joindate AND DATE_ADD(FROM_UNIXTIME(VBU.joindate), INTERVAL 30 DAY)
GROUP BY VBP.userid
ORDER BY NUMBER_OF_POSTS_FOR_30_DAYS DESC"
I have to select the users who have posted the most from when they joined till 30 days after..... and I can't figure out how to do it withouth the FROM_UNIXTIME function..
我必须选择在加入时发布最多的用户,直到30天后……我不知道怎么用FROM_UNIXTIME函数。
But it takes a lot of time. Any thoughts on how to improve the performance for the query?
但这需要很多时间。对于如何提高查询性能有什么想法吗?
Here is the output for explain
这是解释的输出。
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,VBP,index,userid,threadid_visible_dateline,18,NULL,2968000,"Using where; Using index; Using temporary; Using filesort"
1,SIMPLE,VBU,eq_ref,PRIMARY,PRIMARY,4,vb_copilul.VBP.userid,1,"Using where"
And here is the info about the tables
这是关于表格的信息
Table,"Create Table"
vb_user,"CREATE TABLE `vb_user` (
`userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(100) NOT NULL DEFAULT '',
`posts` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`userid`),
KEY `usergroupid` (`usergroupid`),
) ENGINE=MyISAM AUTO_INCREMENT=101076 DEFAULT CHARSET=latin1"
Table,"Create Table"
vb_post,"CREATE TABLE `vb_post` (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`threadid` int(10) unsigned NOT NULL DEFAULT '0',
`parentid` int(10) unsigned NOT NULL DEFAULT '0',
`username` varchar(100) NOT NULL DEFAULT '',
`userid` int(10) unsigned NOT NULL DEFAULT '0',
`title` varchar(250) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`pagetext` mediumtext,
`allowsmilie` smallint(6) NOT NULL DEFAULT '0',
`showsignature` smallint(6) NOT NULL DEFAULT '0',
`ipaddress` char(15) NOT NULL DEFAULT '',
`iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
`visible` smallint(6) NOT NULL DEFAULT '0',
`attach` smallint(5) unsigned NOT NULL DEFAULT '0',
`infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
`reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`postid`),
KEY `userid` (`userid`),
KEY `threadid` (`threadid`,`userid`),
KEY `threadid_visible_dateline` (`threadid`,`visible`,`dateline`,`userid`,`postid`),
KEY `dateline` (`dateline`),
KEY `ipaddress` (`ipaddress`)
) ENGINE=MyISAM AUTO_INCREMENT=3009320 DEFAULT CHARSET=latin1"
1 个解决方案
#1
3
Two things you can do to improve the query:
您可以做两件事来改进查询:
- Do not convert VBP.datetime to unix time. Use the BETWEEN query with the dates directly. In your query the server has to convert all dates in the DB to compare them, instead of use the native types. If you are always using the datetime column as unix timestamp, then declare it as Double (I think?) instead of DATETIME (or TIMESTAMP - whatever you have chosen). This way you will speed up other operations too.
- 不要把VBP。datetime unix时间。直接使用BETWEEN查询和日期。在查询中,服务器必须转换DB中的所有日期来比较它们,而不是使用本机类型。如果您总是使用datetime列作为unix时间戳,那么将它声明为Double(我认为是?),而不是datetime(或timestamp)——无论您选择什么)。这样,您也将加快其他操作。
- Add index to the datetime column to make sure the between query is fast enough.
- 向datetime列添加索引,以确保between查询足够快。
Everything else looks OK
一切看起来好
#1
3
Two things you can do to improve the query:
您可以做两件事来改进查询:
- Do not convert VBP.datetime to unix time. Use the BETWEEN query with the dates directly. In your query the server has to convert all dates in the DB to compare them, instead of use the native types. If you are always using the datetime column as unix timestamp, then declare it as Double (I think?) instead of DATETIME (or TIMESTAMP - whatever you have chosen). This way you will speed up other operations too.
- 不要把VBP。datetime unix时间。直接使用BETWEEN查询和日期。在查询中,服务器必须转换DB中的所有日期来比较它们,而不是使用本机类型。如果您总是使用datetime列作为unix时间戳,那么将它声明为Double(我认为是?),而不是datetime(或timestamp)——无论您选择什么)。这样,您也将加快其他操作。
- Add index to the datetime column to make sure the between query is fast enough.
- 向datetime列添加索引,以确保between查询足够快。
Everything else looks OK
一切看起来好