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.

[pbmods]

Cardinality is an estimated value, which MySQL uses to determine whether to use an index (and which index to use) or do a full scan. Even after running ANALYZE (or OPTIMIZE) TABLE, the index cardinality might not be correct even for small tables. This has to do with how MySQL calculates cardinality (hint: it doesn't run through and count all the unique values).

http://dev.mysql.com/doc/refman/5.0/en/show-index.html

[Motoma]

Try this out:

DROP TABLE IF EXISTS Tester;
CREATE TABLE Tester
(
ID INT(11) NOT NULL AUTO_INCREMENT,
K VARCHAR(10),
U VARCHAR(1),
PRIMARY KEY(ID),
UNIQUE I1 (U),
KEY K1 (K)
)
TYPE = MyISAM;
 
INSERT INTO Tester (K, U) VALUES ('2', '3');
INSERT INTO Tester (K, U) VALUES ('2', '3');
SHOW INDEX FROM Tester;
 

I am not allowed to insert the second row, which leads me to believe the key is being enforced. I would be interested in hearing your ideas on this matter.

[Sandham]

Cardinality is an estimated value, which MySQL uses to determine whether to use an index (and which index to use) or do a full scan. Even after running ANALYZE (or OPTIMIZE) TABLE, the index cardinality might not be correct even for small tables. This has to do with how MySQL calculates cardinality (hint: it doesn't run through and count all the unique values).

http://dev.mysql.com/doc/refman/5.0/en/show-index.html

My test case used only 2 rows, but in my live environment, the same results were there even after inserting more than 2,500 rows.

If MySQL determines its SQL execution plan based on what it knows about index cardinality and it sees NULL, then whether the index is present or not, this would cause the problem I was experiencing. Not too much of an overhead when processing a single table, but my code included multiple tables including a self join on this 2,500 row table.

The documentation for SHOW INDEX implies that ANALYZE TABLE (and I believe also OPTIMIZE TABLE) initially populates the cardinality of an index, but it also seems to be maintained thereafter (accurately in my experience) after every insert, update, delete.

My tables in this database are rebuilt regularly, and the fact that an ANALYZE is needed to activate the index is probably all I need to know to make sure I don't fall into the same trap again.

[Sandham]

Try this out:

DROP TABLE IF EXISTS Tester;
CREATE TABLE Tester
(
ID INT(11) NOT NULL AUTO_INCREMENT,
K VARCHAR(10),
U VARCHAR(1),
PRIMARY KEY(ID),
UNIQUE I1 (U),
KEY K1 (K)
)
TYPE = MyISAM;
 
INSERT INTO Tester (K, U) VALUES ('2', '3');
INSERT INTO Tester (K, U) VALUES ('2', '3');
SHOW INDEX FROM Tester;
 

I am not allowed to insert the second row, which leads me to believe the key is being enforced. I would be interested in hearing your ideas on this matter.

Yes, I agree, the uniqueness of index I1 is enforced, but the cardinality still shows as null. This implies to me that the index is present, but it's no use unless My SQL knows about it when it determines the best execution plan.

[Motoma]

Yes, I agree, the uniqueness of index I1 is enforced, but the cardinality still shows as null. This implies to me that the index is present, but it's no use unless My SQL knows about it when it determines the best execution plan.

This may be the case; have you tried running an EXPLAIN to see if the index is being used in that circumstance?

[Sandham]

This may be the case; have you tried running an EXPLAIN to see if the index is being used in that circumstance?

No, I hadn't run an EXPLAIN on the query, but I have now, using phpMyAdmin.

Firstly, I ran an EXPLAIN on the query with the tables optimized.
Then, I reloaded my data (which performs a TRUNCATE first), and ran the EXPLAIN again.
(Followed very quickly by optimizing all the tables again!)

I was not familiar with the output of EXPLAIN for MySQL, but having read the documentation, my interpretation is as follows.

In both cases, the non-unique keys that I would expect to be used are listed under 'possible_keys'.

In an optimized state, for each reference to the table, one of the non-unique keys is listed under 'key' - the non-unique indexes would be used.

In a non-optimized state, for each reference to the table, only the unique index is shown under 'key' - the non-unique indexes would not be used.