MySql:需要改进查询的性能。

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

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