datetme性能问题

mysql> SELECT id,add_datetime,favorite_count FROM message_message use index(idx_add_fav)  WHERE status = 0  AND category IN (1, 9) AND add_datetime >= '2012-08-03 12:13:50' ORDER BY favorite_count DESC limit 5;
+----------+---------------------+----------------+
| id       | add_datetime        | favorite_count |
+----------+---------------------+----------------+
| 43035311 | 2012-08-28 09:19:37 |           1922 |
| 43035258 | 2012-08-28 09:19:09 |           1815 |
| 43035242 | 2012-08-28 09:19:05 |           1757 |
| 42346151 | 2012-08-24 10:32:50 |           1702 |
| 43035318 | 2012-08-28 09:19:42 |           1679 |
+----------+---------------------+----------------+
5 rows in set (8.24 sec)

mysql> SELECT id,add_datetime,favorite_count FROM message_message use index(idx_add_fav)  WHERE status = 0  AND category IN (1, 9) AND add_datetime >= '2012-09-03 12:13:50' ORDER BY favorite_count DESC limit 5;
+----------+---------------------+----------------+
| id       | add_datetime        | favorite_count |
+----------+---------------------+----------------+
| 44044276 | 2012-09-03 13:11:10 |              4 |
| 44040619 | 2012-09-03 12:35:05 |              3 |
| 44041272 | 2012-09-03 12:41:46 |              2 |
| 44045135 | 2012-09-03 13:19:12 |              2 |
| 44046799 | 2012-09-03 13:32:57 |              2 |
+----------+---------------------+----------------+
5 rows in set (0.10 sec)

mysql> SELECT id,add_datetime,favorite_count FROM message_message   WHERE status = 0  AND category IN (1, 9) AND add_datetime >= '2012-08-03 12:13:50' ORDER BY favorite_count DESC limit 5;
+----------+---------------------+----------------+
| id       | add_datetime        | favorite_count |
+----------+---------------------+----------------+
| 43035311 | 2012-08-28 09:19:37 |           1922 |
| 43035258 | 2012-08-28 09:19:09 |           1815 |
| 43035242 | 2012-08-28 09:19:05 |           1757 |
| 42346151 | 2012-08-24 10:32:50 |           1702 |
| 43035318 | 2012-08-28 09:19:42 |           1679 |
+----------+---------------------+----------------+
5 rows in set (0.02 sec)

mysql> SELECT id,add_datetime,favorite_count FROM message_message   WHERE status = 0  AND category IN (1, 9) AND add_datetime >= '2012-09-03 12:13:50' ORDER BY favorite_count DESC limit 5;
+----------+---------------------+----------------+
| id       | add_datetime        | favorite_count |
+----------+---------------------+----------------+
| 44044276 | 2012-09-03 13:11:10 |              4 |
| 44040619 | 2012-09-03 12:35:05 |              3 |
| 44050902 | 2012-09-03 14:02:20 |              2 |
| 44047630 | 2012-09-03 13:39:12 |              2 |
| 44046799 | 2012-09-03 13:32:57 |              2 |
+----------+---------------------+----------------+
5 rows in set (32.83 sec)

 

explain:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: message_message
         type: index
possible_keys: idx_add_t,idx_add_rep,idx_add_fav
          key: idx_favorite_count
      key_len: 4
          ref: NULL
         rows: 7752
        Extra: Using where
1 row in set (0.00 sec)

 

index:

  KEY `idx_parent` (`parent_id`),
  KEY `idx_last_replied` (`last_replied_datetime`),
  KEY `idx_x8` (`sender_id`,`add_datetime`),
  KEY `idx_add_t` (`add_datetime`),
  KEY `idx_group` (`group_id`),
  KEY `idx_album` (`album_id`),
  KEY `idx_album_add` (`album_id`,`add_datetime`),
  KEY `idx_add_rep` (`add_datetime`,`last_replied_datetime`),
  KEY `idx_pid_addt` (`parent_id`,`add_datetime`),
  KEY `idx_photo_id` (`photo_id`),
  KEY `idx_mm_gmt_up` (`gmt_updated`),
  KEY `idx_favorite_count` (`favorite_count`),
  KEY `idx_status_albumid` (`album_id`,`status`),
  KEY `idx_alb_pk` (`album_id`,`id`),
  KEY `idx_sdl_pk` (`sender_id`,`id`),
  KEY `idx_sdl_cat_fc_pk` (`sender_id`,`favorite_count`),
  KEY `idx_flag` (`flag`),
  KEY `idx_album_fc` (`album_id`,`favorite_count`)