Bug #14709 LOAD DATA INFILE: Indexes get disabled by LOAD DATA
Submitted: 7 Nov 2005 16:30 Modified: 7 Apr 2008 9:08
Reporter: martin koegler
Status: In progress
Category:Server Severity:S3 (Non-critical)
Version:4.1.16-BK, 4.1.14 (4.1.15),5.0.16 OS:Linux (Linux)
Assigned to: Tatjana A. Nuernberg Target Version:
Triage: Triaged: D3 (Medium)

[7 Nov 2005 16: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 16: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 9: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 9:46] martin koegler 
SHOW VARIABLES

Attachment: variables.txt (text/plain), 14.40 KiB.
[11 Nov 2005 10: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 14: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 14:22] Valeriy Kravchuk 
Shell script to repeat the problematic behaviour

Attachment: 14709.sh (application/octet-stream, text), 1.18 KiB.
[27 Nov 2005 14:23] Valeriy Kravchuk 
The results of the shell script

Attachment: 14709.txt (text/plain), 2.09 KiB.
[27 Nov 2005 14: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 4: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 18: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 12: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 8: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
   {