[前][次][番号順一覧][スレッド一覧]

mysql:2776

From: Satoshi Koiwa <Satoshi Koiwa <koiwa@xxxxxxxxxx>>
Date: Fri, 01 Dec 2000 13:50:11 +0900
Subject: [mysql 02776] Re: インデックスの効果について

「北海道」の方が「長野県」よりデータが多いので……。
2値といっても分布は偏っています。

mysql> select flag1, count(*) from ken_all where ad1 = '北海道' group by
flag1;
+-------+----------+
| flag1 | count(*) |
+-------+----------+
| 0     |     7875 |
| 1     |      327 |
+-------+----------+
2 rows in set (1.67 sec)

で、下記のごとくなりました。flag1が0に対する検索と1に対する検索でそれこそ
「劇的」に違います。0の場合は「インデックスを張るだけ邪魔」状態です。
ちなみに他の列には一切インデックスは張ってません。


mysql> select count(*) from ken_all where flag1 = '0' and ad1 = '北海道';
+----------+
| count(*) |
+----------+
|     7875 |
+----------+
1 row in set (5.34 sec)

mysql> create index ken_all_01 on ken_all(ad1);
Query OK, 120162 rows affected (42.07 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql> select count(*) from ken_all where flag1 = '0' and ad1 = '北海道';
+----------+
| count(*) |
+----------+
|     7875 |
+----------+
1 row in set (1.05 sec)

mysql> drop index ken_all_01 on ken_all;
Query OK, 120162 rows affected (16.05 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql> create index ken_all_01 on ken_all(flag1);
Query OK, 120162 rows affected (42.98 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql> select count(*) from ken_all where flag1 = '0' and ad1 = '北海道';
+----------+
| count(*) |
+----------+
|     7875 |
+----------+
1 row in set (5.73 sec)

mysql> drop index ken_all_01 on ken_all;
Query OK, 120162 rows affected (14.50 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql> select count(*) from ken_all where flag1 = '1' and ad1 = '北海道';
+----------+
| count(*) |
+----------+
|      327 |
+----------+
1 row in set (6.49 sec)

mysql> create index ken_all_01 on ken_all(ad1);
Query OK, 120162 rows affected (42.47 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql> select count(*) from ken_all where flag1 = '1' and ad1 = '北海道';
+----------+
| count(*) |
+----------+
|      327 |
+----------+
1 row in set (0.95 sec)

mysql> drop index ken_all_01 on ken_all;
Query OK, 120162 rows affected (16.30 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql> create index ken_all_01 on ken_all(flag1);
Query OK, 120162 rows affected (44.24 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql> select count(*) from ken_all where flag1 = '1' and ad1 = '北海道';
+----------+
| count(*) |
+----------+
|      327 |
+----------+
1 row in set (0.30 sec)

mysql> drop index ken_all_01 on ken_all;
Query OK, 120162 rows affected (15.61 sec)
Records: 120162  Duplicates: 0  Warnings: 0

mysql>


[前][次][番号順一覧][スレッド一覧]

      2736 2000-11-26 14:07 ["Hiroshi Koishi" <pe] インデックスの効果について              
      2739 2000-11-27 19:00 ┣[inano <inano@xxxxxxx]                                       
      2740 2000-11-28 12:23 ┣[Yoshihiko Kobayashi ]                                       
      2766 2000-11-29 22:33 ┗[Satoshi Koiwa <koiwa]                                       
      2768 2000-11-30 00:43  ┗[とみたまさひろ <tomm]                                     
      2771 2000-11-30 09:34   ┣[Satoshi Tatsuoka <sa]                                   
      2772 2000-11-30 10:06   ┣["william" <william@x]                                   
      2775 2000-12-01 12:38   ┣[Satoshi Koiwa <koiwa]                                   
->    2776 2000-12-01 13:50   ┗[Satoshi Koiwa <koiwa]