Indexes disabled by default?

[Peter Wood]

Greetings,

I am running MySQL 5.0.18, compiled from the Community Edition source tarball, on a Solaris 9 system, running on Sparc64 architecture. All of my tables are using the MyISAM engine. I am experiencing an issue where any indexes on my tables are disabled by default.

Here is an example session showing what I did to test this:

~~~~~
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36 to server version: 5.0.18-log

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

mysql> create database test2;
Query OK, 1 row affected (0.00 sec)

mysql> use test2;
Database changed
mysql> create table test_table ( id int not null auto_increment primary key, name varchar(100), email varchar(100) );
Query OK, 0 rows affected (0.01 sec)

mysql> create index emailidx on test_table ( email );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show indexes from test_table\G
*************************** 1. row ***************************
Table: test_table
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment: disabled
*************************** 2. row ***************************
Table: test_table
Non_unique: 1
Key_name: emailidx
Seq_in_index: 1
Column_name: email
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment: disabled
2 rows in set (0.01 sec)

mysql> insert into test_table values ( '','Test User','[e-mail]' );
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> explain select * from test_table where email = '[e-mail]'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_table
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)

mysql> explain select * from test_table force index ( emailidx ) where email = '[e-mail]'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_table
type: system
possible_keys: emailidx
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)

mysql> explain select * from test_table where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_table
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.01 sec)

mysql> explain select * from test_table force index ( PRIMARY ) where id = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_table
type: system
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra:
1 row in set (0.00 sec)

~~~~~

As you can see, the indexes show a Comment of 'disabled', and they are not used when running a query on the table, unless force index is used.

I have tried the following things to solve the issue:
* analyze/optimize table - no effect
* flush tables - no effect
* restarted DB server - no effect
* alter table enable keys - no effect

Does anyone know why these indexes are disabled, and how I can enable them?

Thanks.



Edited 1 time(s). Last edit at 01/27/2006 10:20AM by Peter Wood.