Index disabled after TRUNCATE

[Sandham]

I have a series of tables in which the data can be refreshed from a file.

Before refreshing the data, some of the tables are cleared using either TRUNCATE or DELETE.

If TRUNCATE is used, any index which is not PRIMARY or UNIQUE appears to be disabled and is not updated when new data is inserted into the table.

Performing an OPTIMIZE reactivates the index.

If DELETE is used instead of TRUNCATE, this problem does not occur.

Does anyone know if this a fault, a feature, an option which can be changed?

MySQL version 4.1.20

[pbmods]

This probably has something to with the fact that TRUNCATE actually drops and recreates the table. It does not seem like it is supposed to do that, though, and I wasn't able to Google much about it. Try to see if you can reliably reproduce this behavior in a separate database.

You should also be able to re-enable the indexes by doing:

ALTER TABLE `tablename` ENABLE KEYS
 

[Motoma]

MySQL 4.1 has a lot of quirky 'features' in it. I would suggest upgrading to 5.

[Sandham]

MySQL 4.1 has a lot of quirky 'features' in it. I would suggest upgrading to 5.

Thanks for the advice, but not currently an option.

I'm running on a shared hosting system and because I was losing several indexes, my queries started performing badly (MySQL server overload), and my MySQL database was turned off - twice!

It was only after the second occurrence that I managed to get enough information to find out what was causing the problem.

Oh, and I have only just recently signed up with this host - the previous one suspended my account completely (e-mail, web, FTP, My SQL), again for MySQL server overload, presumably for the same reason.

I have a workround now, but you can see why I'm keen to understand the cause.

[Motoma]

I have been in the same boat. It is really frustrating when you have to use an outdated version of MySQL, especially if you are stuck with one that does not support stored procedures or subqueries.

[Sandham]

This probably has something to with the fact that TRUNCATE actually drops and recreates the table. It does not seem like it is supposed to do that, though, and I wasn't able to Google much about it. Try to see if you can reliably reproduce this behavior in a separate database.

You should also be able to re-enable the indexes by doing:

ALTER TABLE `tablename` ENABLE KEYS
 

After experimenting, it is not the TRUNCATE which disables the INDEXes - they are not activated from the time of the CREATE TABLE.

I created a copy of my table in another database.

There is a PRIMARY INDEX, a UNIQUE INDEX and 5 other INDEXes.

Running an OPTIMIZE TABLE or ANALYZE TABLE on the empty table returns 'Table is already up to date'.

INSERT INTO one or more rows and the PRIMARY INDEX and UNIQUE INDEX are updated - the other INDEXes are not.

ALTER TABLE `tablename` ENABLE KEYS has no effect .
Perhaps the INDEXES are not 'disabled', but they are certainly not active.

The only actions that seems to activate the INDEXes is OPTIMIZE TABLE or ANALYZE TABLE.

Once one of these is done, the INDEXes are maintained after any UPDATE, INSERT or DELETE.

TRUNCATE obviously drops and creates the table, and everything starts again.

Confused...

[Motoma]

Could you build a simple test case that demonstrates this behavior? I would like to test this out, if you could write up the SQL statements starting from your create table, I would be very appreciative.

[Sandham]

Could you build a simple test case that demonstrates this behavior? I would like to test this out, if you could write up the SQL statements starting from your create table, I would be very appreciative.

I discarded the scripts and the data that I used to perform the test, but it was based on the table that had originally caused me the problem. There were 35 columns in this table, and some of it was sensitive data.

I will look at recreating a simple test case when I get chance, possibly later today.

[Sandham]

Test case, simplified to the minimum below: -

DROP TABLE IF EXISTS Tester;
 
CREATE TABLE Tester
(
 ID INT(11) NOT NULL,
 F1 VARCHAR(10) NOT NULL,
 F2 VARCHAR(10) NOT NULL,
 F3 VARCHAR(10) NOT NULL,
 F4 VARCHAR(10) NOT NULL,
 F5 DATETIME NOT NULL,
 F6 TEXT NOT NULL,
 PRIMARY KEY (ID),
 UNIQUE I1 (F1, F2),
 KEY I2 (F3, F4),
 KEY I3 (F4),
 KEY I4 (F1, F3, F4),
 KEY I5 (F1, F4),
 KEY I6 (F5)
)
TYPE = MYISAM;

SHOW INDEX FROM Tester;

Result: (PRIMARY and I1 cardinality 0; I2, I3, I4, I5, I6 cardinality NULL)

INSERT INTO Tester VALUES ('1', 'A1', 'A2', 'A3', 'A4', '2007-05-29', 'A5');

SHOW INDEX FROM Tester;

Result: (PRIMARY and I1 cardinality 1; I2, I3, I4, I5, I6 cardinality NULL)

OPTIMIZE TABLE Tester;

SHOW INDEX FROM Tester;

Result: (PRIMARY, I1, I2, I3, I4, I5, I6 all cardinality 1)
Note: ANALYZE TABLE Tester; has the same effect as OPTIMIZE TABLE Tester;

INSERT INTO Tester VALUES ('2', 'B1', 'B2', 'B3', 'B4', '2007-05-29', 'B5');

SHOW INDEX FROM Tester;

Result: (PRIMARY, I1, I2, I3, I4, I5, I6 all cardinality 2)

[Motoma]

Thank you. I was able to reproduce this behavior in MySQL 4.0.16-nt.
One thing I noticed, however, this behavior is not present with the InnoDB database engine.