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

mysql:548

From: Tatsuya Ina <Tatsuya Ina <ina@xxxxxxxxxx>>
Date: Thu, 14 Jan 1999 10:35:32 GMT
Subject: [mysql 548] Re: mMySQL speed


伊奈です。みなさん色々ありがとうございます。

> 毅です
> 
> このときの詳細情報がほしいのですが、以下でとって
> 送ってくれませんか?もう少し詳しく検討したいのです。
> 
> 1. クエリを explain した結果

mysql> explain select count(*) from Test_all_tab where lotno like 'D15A0%';
+--------------+-------+-------------------+-------------+---------+------+--------+-------+
| table        | type  | possible_keys     | key         | key_len | ref  | rows   | Extra |
+--------------+-------+-------------------+-------------+---------+------+--------+-------+
| Test_all_tab | range | lotno_index,param | lotno_index |    NULL | NULL | 585298 |       |
+--------------+-------+-------------------+-------------+---------+------+--------+-------+
1 row in set (0.36 sec)

mysql> select count(*) from Test_all_tab where stop_day >'1998-11-14' and stop_day <'1998-12-14';
+----------+
| count(*) |
+----------+
|  1083600 |
+----------+
1 row in set (4 min 45.01 sec)

betweenの時よりは100sec程度速くなっています。

mysql> explain select count(*) from Test_all_tab where stop_day >'1998-11-14' and stop_day <'1998-12-14';
+--------------+-------+----------------+----------------+---------+------+---------+---------------+
| table        | type  | possible_keys  | key            | key_len | ref  | rows    | Extra         |
+--------------+-------+----------------+----------------+---------+------+---------+---------------+
| Test_all_tab | range | stop_day_index | stop_day_index |    NULL | NULL | 1059053 | ; Using index |
+--------------+-------+----------------+----------------+---------+------+---------+---------------+
1 row in set (0.01 sec)

> 2. mysqlbug の実行結果
これってこれでいいのでしょうか。

SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: ina
To: mysql@xxxxxxxxxx
Subject: [50 character or so descriptive subject here (for reference)]

>Description:
	<precise description of the problem (multiple lines)>
>How-To-Repeat:
	<code/input/activities to reproduce the problem (multiple lines)>
>Fix:
	<how to correct or work around the problem, if known (multiple lines)>

>Submitter-Id:	<submitter ID>
>Originator:	Tatsuya Ina
>Organization:
 <organization of PR author (multiple lines)>
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:	<synopsis of the problem (one line)>
>Severity:	<[ non-critical | serious | critical ] (one line)>
>Priority:	<[ low | medium | high ] (one line)>
>Category:	mysql
>Class:		<[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release:	mysql-3.22.14b-gamma (Source distribution)

>Environment:
	<machine, os, target, libraries (multiple lines)>
System: Linux ickg140 2.0.33 #8 Thu Nov 19 10:15:24 GMT 1998 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-unknown-linux-gnulibc1/2.7.2.3/specs
gcc version 2.7.2.3
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
Configure command: ./configure 
Perl: This is perl, version 5.004_04 built for i586-linux
EUC version


> 3. 以下のように実行したときの結果
> ----------
> show status;

mysql> show status;
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Aborted_clients        | 2        |
| Aborted_connects       | 2        |
| Created_tmp_tables     | 0        |
| Deletes                | 0        |
| Flush_commands         | 1        |
| Key_blocks_used        | 976      |
| Key_read_requests      | 39714872 |
| Key_reads              | 134512   |
| Key_write_requests     | 11867142 |
| Key_writes             | 189863   |
| Not_flushed_key_blocks | 0        |
| Open_tables            | 9        |
| Open_files             | 17       |
| Open_streams           | 0        |
| Opened_tables          | 19       |
| Questions              | 160      |
| Read_key               | 9        |
| Read_next              | 5891858  |
| Read_rnd               | 19388740 |
| Read_first             | 0        |
| Running_threads        | 1        |
| Slow_queries           | 13       |
| Uptime                 | 55212    |
| Write                  | 9694362  |
+------------------------+----------+
24 rows in set (0.00 sec)

> select count(*) from Test_all_tab where lotno like 'D15A0%';

mysql> select count(*) from Test_all_tab where lotno like 'D15A0%';
+----------+
| count(*) |
+----------+
|   717505 |
+----------+
1 row in set (8 min 57.49 sec)

> select count(*) from Test_all_tab where stop_day between '1998-11-14
> 00:00:00' and '1998-12-14 23:59:59';

mysql> select count(*) from Test_all_tab where stop_day between '1998-11-14 00:00:00' and '1998-12-14 23:59:59';
+----------+
| count(*) |
+----------+
|  1118730 |
+----------+
1 row in set (4 min 59.42 sec)

> explain select count(*) from Test_all_tab where lotno like 'D15A0%';

mysql> explain select count(*) from Test_all_tab where lotno like 'D15A0%';+--------------+-------+-------------------+-------------+---------+------+--------+-------+
| table        | type  | possible_keys     | key         | key_len | ref  | rows   | Extra |
+--------------+-------+-------------------+-------------+---------+------+--------+-------+
| Test_all_tab | range | lotno_index,param | lotno_index |    NULL | NULL | 585298 |       |
+--------------+-------+-------------------+-------------+---------+------+--------+-------+
1 row in set (0.32 sec)

> explain select count(*) from Test_all_tab where stop_day between '1998-11-14 00:00:00' and '1998-12-14 23:59:59';

mysql> explain select count(*) from Test_all_tab where stop_day between '1998-11-14 00:00:00' and '1998-12-14 23:59:59';
+--------------+-------+----------------+----------------+---------+------+---------+---------------+
| table        | type  | possible_keys  | key            | key_len | ref  | rows    | Extra         |
+--------------+-------+----------------+----------------+---------+------+---------+---------------+
| Test_all_tab | range | stop_day_index | stop_day_index |    NULL | NULL | 1093409 | ; Using index |
+--------------+-------+----------------+----------------+---------+------+---------+---------------+
1 row in set (0.00 sec)

> show status;

mysql> show status
    -> ;
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Aborted_clients        | 2        |
| Aborted_connects       | 2        |
| Created_tmp_tables     | 0        |
| Deletes                | 0        |
| Flush_commands         | 1        |
| Key_blocks_used        | 976      |
| Key_read_requests      | 39850677 |
| Key_reads              | 157305   |
| Key_write_requests     | 11867142 |
| Key_writes             | 189863   |
| Not_flushed_key_blocks | 0        |
| Open_tables            | 9        |
| Open_files             | 17       |
| Open_streams           | 0        |
| Opened_tables          | 19       |
| Questions              | 165      |
| Read_key               | 11       |
| Read_next              | 7728093  |
| Read_rnd               | 19388740 |
| Read_first             | 0        |
| Running_threads        | 1        |
| Slow_queries           | 15       |
| Uptime                 | 56346    |
| Write                  | 9694362  |
+------------------------+----------+
24 rows in set (0.00 sec)


実は疑問がひとつあって、テーブル定義では
     index lotno_index (lotno),
     index stop_day_index (stop_day),
     unique param (lotno,order_no,param,ope));

のようにlotnoにインデックスを作成しているつもりですが,そのあと
lotno,order_no,param,stop_dayの4つでunique indexを作成しています。
show columnsでみてみると

mysql> show columns from Test_all_tab;
+-----------+-------------+------+-----+---------------------+-------+
| Field     | Type        | Null | Key | Default             | Extra |
+-----------+-------------+------+-----+---------------------+-------+
| lotno     | char(22)    |      | PRI |                     |       |
| order_no  | smallint(6) |      | PRI | 0                   |       |
| param     | char(16)    |      | PRI |                     |       |
| ope       | char(12)    |      | PRI |                     |       |
| start_day | datetime    |      |     | 0000-00-00 00:00:00 |       |
| stop_day  | datetime    |      | MUL | 0000-00-00 00:00:00 |       |
| pc_type   | char(16)    |      |     |                     |       |
| pd        | char(16)    |      |     |                     |       |
| kubun     | char(16)    |      |     |                     |       |
| pc_no     | smallint(6) |      |     | 0                   |       |
| good_wf   | smallint(6) |      |     | 0                   |       |
| ng_wf     | smallint(6) |      |     | 0                   |       |
| wfno      | smallint(6) |      |     | 0                   |       |
| value     | char(64)    |      |     |                     |       |
+-----------+-------------+------+-----+---------------------+-------+

となっており、lotno,order_no,param,opeの4つの組合せでのprimari keyしか
みえません。こういう定義の仕方をすると、lotno単体に対して作成したindexが
無効になってしまうなんてことはないのでしょうか?
それともただshow columnsコマンドの結果の表示の仕方の問題だけでしょうか?



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