我有一个带有权重日志的表,其中包含这些字段(以及其他字段)和示例数据:
userid date weight
---------------------------
1 2015-01-10 100
1 2015-01-15 90
1 2015-01-20 80
2 2015-01-05 120
2 2015-01-15 110
3 2015-01-15 150
3 2015-01-24 140
我需要在两次约会之间减肥。目前,在2015-01-09和2015年01月21日之间,我在PHP中使用此函数:
function get_weight_loss($userid, $start_date, $end_date) {
global $db;
$query = "
SELECT date, weight FROM weight_tracker_log
WHERE date=(
SELECT MAX(date) FROM weight_tracker_log
WHERE date <= '$start_date' AND userid = $userid
) AND userid = $userid
";
$result = mysql_query($query, $db);
$previous = mysql_fetch_assoc($result);
if ( !$previous['date'] ) {
/* Get first entry for user */
$query = "
SELECT date, weight FROM weight_tracker_log
WHERE date=(
SELECT MIN(date) FROM weight_tracker_log
WHERE userid = $userid
) AND userid = $userid
";
$result = mysql_query($query, $db);
$previous = mysql_fetch_assoc($result);
};
$query = "
SELECT date, weight FROM weight_tracker_log
WHERE date=(
SELECT MAX(date) FROM weight_tracker_log
WHERE date <= '$end_date' AND userid = $userid
) AND userid = $userid
";
$result = mysql_query($query, $db);
$next = mysql_fetch_assoc($result);
mysql_free_result($result);
if ( $previous['date'] && $next['date'] && $previous['date']<$next['date'] && $next['date']>$start_date) {
return $previous['weight'] - $next['weight'];
} else {
return 0;
};
};
它运行良好,但计算时间越来越长。它的作用是:
- 查找用户在
$start_date
之前的最新条目 - 如果它不存在,则:查找
$start_date
之后的第一个用户条目 - 在范围内查找最后一个条目(并获取权重)
- 如果第一个日期小于第二个,则计算权重差
有什么方法可以优化查询或算法本身吗?
表格是这样创建的:
CREATE TABLE `weight_tracker_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL COMMENT 'xcart_cutomers',
`weight` int(11) NOT NULL COMMENT 'grams',
`date` date NOT NULL COMMENT 'YYYY-MM-DD',
`is_start_weight` char(1) NOT NULL DEFAULT 'N',
`is_end_weight` char(1) NOT NULL DEFAULT 'N',
`image` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=97526 DEFAULT CHARSET=utf8
我在您的表上没有看到任何索引。这意味着每个SELECT都必须遍历整个表才能找到受影响的行。想象一下,如果你不得不翻阅一本500页的书的每一页,只为了找到与狗有关的两页。
您需要在date
和userid
列上添加索引:
CREATE INDEX weight_tracker_log_date ON weight_tracker_log ( date );
CREATE INDEX weight_tracker_log_userid ON weight_tracker_log ( userid );
http://use-the-index-luke.com/是对如何以及为什么使用索引的极好介绍。