mysqlインデックス

[tukeJonny]

No description provided.

[tukeJonny]

SELECT * FROM message WHERE id > 0 AND channel_id = 884 ORDER BY id DESC LIMIT 100\G

mysql> ALTER TABLE message ADD INDEX idx_message_id_channel_id(channel_id, id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN EXTENDED SELECT * FROM message WHERE id > 0 AND channel_id = 884 ORDER BY id DESC LIMIT 100;
+----+-------------+---------+------------+-------+--------------------------------------------------+---------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                                    | key                       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+--------------------------------------------------+---------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | message | NULL       | range | PRIMARY,idx_message_id,idx_message_id_channel_id | idx_message_id_channel_id | 17      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+--------------------------------------------------+---------------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> mysql> EXPLAIN EXTENDED SELECT * FROM message WHERE id > 0 AND channel_id = 884 ORDER BY id DESC LIMIT 100 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: message
   partitions: NULL
         type: range
possible_keys: PRIMARY,idx_message_id,idx_message_id_channel_id
          key: idx_message_id_channel_id
      key_len: 17
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 2 warnings (0.00 sec)

[tukeJonny]

mysql> EXPLAIN SELECT * FROM message WHERE channel_id = 903 ORDER BY id DESC LIMIT 20 OFFSET 0 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: message
   partitions: NULL
         type: ref
possible_keys: idx_message_id_channel_id
          key: idx_message_id_channel_id
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

mysql>