mysql按照时间查询不要使用subdate函数,因为每次subdate的结果值都不同,导致无法query_cache:
这条SQL:
执行三次:
24 rows in set (1.01 sec)
24 rows in set (0.98 sec)
24 rows in set (0.95 sec)
修改成:
执行三次:
24 rows in set (0.98 sec)
24 rows in set (0.00 sec)
24 rows in set (0.00 sec)
不建索引:
select id,add_datetime,favorite_count from message_message where add_datetime > '2012-08-30 12:13:41' and category IN (1, 9) order by favorite_count DESC limit 24 ;
24 rows in set (50.44 sec)
24 rows in set (0.00 sec)
24 rows in set (0.00 sec)
explain select id,add_datetime,favorite_count from message_message where add_datetime > '2012-08-30 12:13:41' and category IN (1, 9) order by favorite_count DESC limit 24 \G;
------------------------------------------------------
id: 1
select_type: SIMPLE
table: message_message
type: index
possible_keys: idx_add_t,idx_add_rep,idx_cat_add_fav
key: idx_favorite_count
key_len: 4
ref: NULL
rows: 48505
Extra: Using where
1 row in set (0.00 sec)
explain 信息:
id: 1
select_type: SIMPLE
table: message_message
type: range
key: idx_cat_add_fav
key_len: 10
ref: NULL
rows: 34899
Extra: Using where; Using index; Using filesort
idx_cat_add_fav(`category`,`add_datetime`,`favorite_count`)
这条sql不知道为什么非常慢
执行计划:
Extra Using where; Using index; Using filesort
key: idx_cat_add_fav
type: range
索引:
idx_cat_add_fav(`category`,`add_datetime`,`favorite_count`)
count: 17400
这条SQL:
select id,add_datetime,favorite_count from message_message
where add_datetime > SUBDATE(now(),INTERVAL 25 HOUR)
and category IN (1, 9) order by favorite_count desc limit 24;
执行三次:
24 rows in set (1.01 sec)
24 rows in set (0.98 sec)
24 rows in set (0.95 sec)
修改成:
SELECT id,add_datetime,favorite_count from message_message
where add_datetime > '2012-08-29 13:44:44' and category IN (1, 9)
order by favorite_count desc limit 24;
执行三次:
24 rows in set (0.98 sec)
24 rows in set (0.00 sec)
24 rows in set (0.00 sec)
不建索引:
select id,add_datetime,favorite_count from message_message where add_datetime > '2012-08-30 12:13:41' and category IN (1, 9) order by favorite_count DESC limit 24 ;
24 rows in set (50.44 sec)
24 rows in set (0.00 sec)
24 rows in set (0.00 sec)
explain select id,add_datetime,favorite_count from message_message where add_datetime > '2012-08-30 12:13:41' and category IN (1, 9) order by favorite_count DESC limit 24 \G;
------------------------------------------------------
id: 1
select_type: SIMPLE
table: message_message
type: index
possible_keys: idx_add_t,idx_add_rep,idx_cat_add_fav
key: idx_favorite_count
key_len: 4
ref: NULL
rows: 48505
Extra: Using where
1 row in set (0.00 sec)
explain 信息:
id: 1
select_type: SIMPLE
table: message_message
type: range
key: idx_cat_add_fav
key_len: 10
ref: NULL
rows: 34899
Extra: Using where; Using index; Using filesort
idx_cat_add_fav(`category`,`add_datetime`,`favorite_count`)
select id,add_datetime,favorite_count from message_message where
add_datetime > '2012-08-29 13:43:44' and category IN (1, 9) order by favorite_count limit 1,10;
这条sql不知道为什么非常慢
执行计划:
Extra Using where; Using index; Using filesort
key: idx_cat_add_fav
type: range
索引:
idx_cat_add_fav(`category`,`add_datetime`,`favorite_count`)
select count(*) from message_message where add_datetime > '2012-08-29 13:43:44' and category IN (1, 9)
count: 17400