Bug #14709 LOAD DATA INFILE: Indexes get disabled by LOAD DATA
Submitted: 7 Nov 2005 15:30 Modified: 4 Feb 2010 11:56
Reporter: martin koegler Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.16-BK, 4.1.14 (4.1.15),5.0.16 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[7 Nov 2005 15:30] martin koegler
Description:
If multiple LOAD DATA statements are executed at the same time, indexes can be disabled after the execution of the LOAD DATA statement. The data on the disk is correct, only the cached information is wrong. Additionally, a SHOW INDEX must be executed on the table simultanly. The query cache is not the problem.

As far as I have seen, also after the execution of an INSERT statement (INSERT INTO xx SELECT * FROM ), the indexes of the target table can also be disabled.
I think, that SHOW INDEX is not the only command, which can trigger this problem. In a real MySQL setup, CHECK TABLE xx QUICK FAST or a SELECT/INSERT/UPDATE or an other LOAD DATA statement  must be the trigger.

How to repeat:
As server for the example, I used MySQL-server-4.1.14-0.glibc23.x86_64.rpm (from dev.mysql.com). Self compiled version of MySQL 4.1.14 and 4.15 (SuSE 9.3 Professional) show similar results.

Create tables:

SQL:
use test;
CREATE TABLE t1 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz char(60) default NULL, KEY (xx,yy),  KEY (yy,xx),  KEY (zz,xx), KEY (yy,zz)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t2 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz char(60) default NULL, KEY (xx,yy),  KEY (yy,xx),  KEY (zz,xx), KEY (yy,zz)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t3 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz char(60) default NULL, KEY (xx,yy),  KEY (yy,xx),  KEY (zz,xx), KEY (yy,zz)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t4 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz char(60) default NULL, KEY (xx,yy),  KEY (yy,xx),  KEY (zz,xx), KEY (yy,zz)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t5 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz char(60) default NULL, KEY (xx,yy),  KEY (yy,xx),  KEY (zz,xx), KEY (yy,zz)) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE t6 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz char(60) default NULL, KEY (xx,yy),  KEY (yy,xx),  KEY (zz,xx), KEY (yy,zz)) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Prepare Load file:
echo '"1234567";"223";"cxycxcxxxc"' >xx
cat xx xx > yy; mv yy xx (repeat this statement, until the size of xx is >=116MB)
ln -s xx t1
ln -s xx t2
ln -s xx t3
ln -s xx t4
ln -s xx t5
ln -s xx t6

for a in t1 t2 t3 t4 t5 t6; do mysqlimport -h 127.0.0.1 -u root test /tmp/mysql/$a & done

While the LOAD DATA STATEMENTS are running, execute on the MySQL command line:
show index from test.t1; show index from test.t2; show index from test.t3; show index from test.t4; show index from test.t5; show index from test.t6;

Wait, until all LOAD commands finish.

mysql> show index from test.t1; show index from test.t2; show index from test.t3; show index from test.t4; show index from test.t5; show index from test.t6;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| t1    |          1 | xx       |            1 | xx          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | xx       |            2 | yy          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy       |            1 | yy          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy       |            2 | xx          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | zz       |            1 | zz          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | zz       |            2 | xx          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy_2     |            1 | yy          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy_2     |            2 | zz          | A         |        NULL |     NULL | NULL   | YES  | BTREE      | disabled |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
8 rows in set (0.00 sec)
(same for t2-t6)

mysql> explain select * from t6 where zz=222;
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | t6    | ALL  | NULL          | NULL |    NULL | NULL | 16777216 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t6 where zz=222;
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | t6    | ALL  | zz            | NULL |    NULL | NULL | 16777216 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> show index from test.t1; show index from test.t2; show index from test.t3; show index from test.t4; show index from test.t5; show index from test.t6;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          1 | xx       |            1 | xx          | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | xx       |            2 | yy          | A         |     4194304 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | yy       |            1 | yy          | A         |     4194304 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | yy       |            2 | xx          | A         |     4194304 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | zz       |            1 | zz          | A         |     4194304 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | zz       |            2 | xx          | A         |     4194304 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | yy_2     |            1 | yy          | A         |     4194304 |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | yy_2     |            2 | zz          | A         |     4194304 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
8 rows in set (0.00 sec)
(same for t2-t6)

mysql> show variables like 'q%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)
[10 Nov 2005 15:40] Valeriy Kravchuk
Thank you for a problem report. 

I was able to repeat the "disabling" using much less data (about 30 Mb) on 4.0.16-BK build on x86 (32bit), but then, after loading finished, select ... where xx=222 was performed using index, while select ... zz=222 - not (according to explain). So, I can't say that all indexes are disabled really...

I need much smaller and exact test case to be able to verify this. Do you have any ideas on how to reduce it? You my.cnf content may be of some use too.
[11 Nov 2005 8:45] martin koegler
>I was able to repeat the "disabling" using much less data (about 30 Mb) on
>4.0.16-BK build on x86 (32bit), but then, after loading finished, select ...
>where xx=222 was performed using index, while select ... zz=222 - not (according
> to explain). So, I can't say that all indexes are disabled really...

I hope, you mean 4.1.16-BK (on 4.0.X I do similar things and never notice such problems).

I tried to narrow it down. Using only one table seems to be enough. The size of the load file must be large enough, so that you can issue the show index command, while the first phase of the LOAD DATA statement is executed. If you are too late, no index gets disables (or only some). My minimal size was 15 MB, but then some times I was too slow. So I use bigger files to have more time.
I use before each attempt
delete from t1; flush tables;

The I run (same setup as last time)
$mysqlimport -h 127.0.0.1 -u root test  /tmp/mysql/t1
and as fast a I can, I issue
show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| t1    |          1 | xx       |            1 | xx          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | xx       |            2 | yy          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy       |            1 | yy          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy       |            2 | xx          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | zz       |            1 | zz          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | zz       |            2 | xx          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy_2     |            1 | yy          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
| t1    |          1 | yy_2     |            2 | zz          | A         |           0 |     NULL | NULL   | YES  | BTREE      | disabled |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
8 rows in set (0.00 sec)

After mysqlimport finishes, all indexes are disabled (if you were fast enough, so that show index returned disabled for each line):

mysql> explain select * from t1 where zz=222; explain select * from t1 where yy=222; explain select * from t1 where xx=222;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL |    NULL | NULL | 1048576 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL |    NULL | NULL | 1048576 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL |    NULL | NULL | 1048576 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from t1 where zz=222; explain select * from t1 where yy=222; explain select * from t1 where xx=222;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1    | ALL  | zz            | NULL |    NULL | NULL | 1048576 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | yy,yy_2       | yy   |       3 | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | xx            | xx   |      14 | const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

So before the flush tables, no index is used, after that some indexes are considered (even if for zz=222 key is NULL).

I fear, that the test case can not made smaller than this, because one LOAD DATA statement must be executed simultanly to a SHOW INDEX command with certain timing requirements. Now, only debugging can show futher details.

My my.ini is empty (except changing the datadir, socket and port to be able to  run a test instance). I will a show variables in the files tab.

Do you need more information?
[11 Nov 2005 8:46] martin koegler
SHOW VARIABLES

Attachment: variables.txt (text/plain), 14.40 KiB.

[11 Nov 2005 9:58] Valeriy Kravchuk
Yes, I meant 4.1.16-BK. 

Thank you for the additional information. Now everything seems clear. Let me try to repeat on a newer build.
[13 Nov 2005 13:54] Valeriy Kravchuk
Verified just as described in last comment on 4.1.16-BK build (ChangeSet@1.2465.1.1, 2005-11-10 15:12:22+01:00, ...) on Linux (with much smaller table t1 on my old Fedora Core 1 box):

[openxs@Fedora 4.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1 (xx decimal(12,0) default NULL,yy smallint(6) default NULL,zz
    -> char(60) default NULL, KEY (xx,yy),  KEY (yy,xx),  KEY (zz,xx), KEY (yy,zz))
    -> ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
[openxs@Fedora 4.1]$ bin/mysqlimport -h 127.0.0.1 -u root test /tmp/t1 &
[2] 3090
[openxs@Fedora 4.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| t1    |          1 | xx       |            1 | xx          | A         |  NULL |     NULL | NULL   | YES  | BTREE      | disabled |
...
| t1    |          1 | yy_2     |            2 | zz          | A         |  NULL |     NULL | NULL   | YES  | BTREE      | disabled |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
8 rows in set (0.00 sec)

mysql> select count(*) from t1; -- this statement will be locked until LOAD completed
test.t1: Records: 163840  Deleted: 0  Skipped: 0  Warnings: 491520
+----------+
| count(*) |
+----------+
|   163840 |
+----------+
1 row in set (4.38 sec)

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment  |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
| t1    |          1 | xx       |            1 | xx          | A         |  NULL |     NULL | NULL   | YES  | BTREE      | disabled |
...
| t1    |          1 | yy_2     |            2 | zz          | A         |  NULL |     NULL | NULL   | YES  | BTREE      | disabled |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+
8 rows in set (0.00 sec)

So, yes, indexes remain disabled after the LOAD finished:

mysql> explain select * from t1 where zz=222; explain select * from t1 where
yy=222; explain select * from t1 where xx=222;

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL |    NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.06 sec)

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL |    NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL |    NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t1 where zz=222; explain select * from t1 where
yy=222; explain select * from t1 where xx=222;

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t1    | ALL  | zz            | NULL |    NULL | NULL | 163840 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.01 sec)

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | yy,yy_2       | yy   |       3 | const |1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.01 sec)

+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | xx            | xx   |      14 | const |1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Now some indexes are used finally.
[27 Nov 2005 13:22] Valeriy Kravchuk
Shell script to repeat the problematic behaviour

Attachment: 14709.sh (application/octet-stream, text), 1.18 KiB.

[27 Nov 2005 13:23] Valeriy Kravchuk
The results of the shell script

Attachment: 14709.txt (text/plain), 2.09 KiB.

[27 Nov 2005 13:29] Valeriy Kravchuk
I had uploaded a shell script and the results it produce to simplify working on this problem. 

Please note that some changes may be needed to pathnames and number of cycles to create large enough file.
[18 Dec 2005 3:18] Alexander Pachev
LOAD DATA INFILE uses the bulk insert algorithm which temporarily disables all non-unique keys for the duration of the load. If it did not, the load time would increase. In some cases, though, it may be preferred to the performance decrease that comes from disabling the keys. In the future versions we should add an option to LOAD DATA INFILE to not use the bulk insert.
[18 Dec 2005 17:20] Martin Kögler
Is INSERT into xxx select * from yyy also temporary disabling the indexes, like LOAD DATA? I see a similar behaviour for tables, where data is added by this mean, so this statement may also need such an option.

I think, that such an option an impraticable solution:
* If the disable index options is activ, a user with SELECT access to a table can accidentaly disable its indices, if he does not verify, that there is no concurrent bulk insert. 
* If the disable index option is not active, all bulk inserts will take longer.

So users, which need fast bulk inserts, must switch to MySQL 4.0 (A workload, which causes these problems under 4.1, runs on a MySQL 4.0.x Windows without any problems) or maybe 5.0 (not tested, if affected)?

As far as I know, no read/write access to a table is possible, while a LOAD DATA is in progress. So would it be a better solution to deny/block all open attempts during the LOAD DATA, so that no wrong information about the indexes can be cached?
[19 Dec 2005 11:16] martin koegler
MySQL 5.0.16 has the same problem. So bulk inserts only work on 4.0.X without any problems.

The following output was created by the script in this bug report and MySQL 5.0.16-max (precompiled by MySQL.com).
Beside some changes to the path of the script, I need to sleep some seconds after the start of mysqlimport. Everything es list unchanged.

Variable_name   Value
version 5.0.16-max
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part Packed   Null    Index_type      Comment
t1      1       xx      1       xx      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       xx      2       yy      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy      1       yy      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy      2       xx      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       zz      1       zz      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       zz      2       xx      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy_2    1       yy      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy_2    2       zz      A       NULL    NULL    NULL    YES     BTREE   disabled
test.t1: Records: 4194304  Deleted: 0  Skipped: 0  Warnings: 8388608
count(*)
4194304
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part Packed   Null    Index_type      Comment
t1      1       xx      1       xx      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       xx      2       yy      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy      1       yy      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy      2       xx      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       zz      1       zz      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       zz      2       xx      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy_2    1       yy      A       NULL    NULL    NULL    YES     BTREE   disabled
t1      1       yy_2    2       zz      A       NULL    NULL    NULL    YES     BTREE   disabled
Execution plans after loading
Execution plans after loading
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    4194304 Using where
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    4194304 Using where
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    4194304 Using where
Execution plans after flushing
Execution plans after flushing
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ALL     zz      NULL    NULL    NULL    4194304 Using where
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ref     yy,yy_2 yy      3       const   1       Using where
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      ref     xx      xx      7       const   1       Using where
+ echo 'So, indexes are not even considered until fuls tables performed. Is it OK?'
So, indexes are not even considered until fuls tables performed. Is it OK?
[21 Dec 2005 7:42] martin koegler
The problem is, that apart from the TABLE instance used by the LOAD DATA statement, the other statements use a second TABLE instance.

When the indexes are enabled, info(HA_STATUS_CONST); is called, which only correct the index information for one TABLE instance. To invalidate the other copies, they are removed from the table cache.

--- mysql-4.1.15/sql/ha_myisam.cc.orig  2005-12-20 10:19:19.098422576 +0100
+++ mysql-4.1.15/sql/ha_myisam.cc       2005-12-20 10:44:21.375041816 +0100
@@ -918,6 +918,7 @@
   }
   else if (mode == HA_KEY_SWITCH_NONUNIQ_SAVE)
   {
+    TABLE_LIST flush;
     THD *thd=current_thd;
     MI_CHECK param;
     const char *save_proc_info=thd->proc_info;
@@ -939,6 +940,15 @@
     }
     info(HA_STATUS_CONST);
     thd->proc_info=save_proc_info;
+
+    /* flush table to invalidate all copies */
+    bzero (&flush, sizeof(TABLE_LIST));
+    if (table)
+    {
+      flush.real_name = table->real_name;
+      flush.db = table->table_cache_key;
+      close_cached_tables (thd, 0, &flush);
+    }
   }
   else
   {