MySQL 4 - Indexes issues

Well, I have to use MySQL 4 in production at work, and well, they is something weird about this.

To speed up copy process I create table without indexes. It really speed up copy, 6x times faster or more (5 min instead of 1h) !

Just before using in compute process, I add the indexes I need :

ALTER TABLE my_table ADD INDEX(a,b,c), ADD INDEX(a,d,e) ...

Seems ok, so now I do my request.

First weird think, MySQL don't always use the best index. Ex :

SELECT a FROM TABLE where b=1;

I have several indexes :

• b
• b,c,d
• b,d,e,f

And it not use the first one! Well, if it's ok, why not, but I don't get it.

So but when it use the third one, the query take almost 10 min instead of few seconds. I have test it on MySQL 5 with same indexes, and it work very well.

So it's just awfull and take a huge amount of time. I complete compute take more time that without any index ! What the hell.

So I have take a look, and seem the statistic for index is not properly build after an "ALTER TABLE ADD INDEX" !

The solution we have found is to do a "CHECK TABLE" after the ALTER TABLE.

Now it use the same index, but the speed is as we expected.

I don't know if this issue is really an issue or we have to do this on indexes after adding it to table. But it's really annoying.

If any body have an explanation, I will really glad to ear it !