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コマンドの結果の表示の仕方の問題だけでしょうか？