recently we came across some weird problem. We changed the fieldlength of a bunch of tables from 45 to 255. After this the time for one query with a JOIN did at least take 10 times longer. Before the time was half or max a second. After we got consistently to 12 seconds.
After some time we did a ANALYZE TABLE and it worked as before. We also could reproduce this behavoiur when we changed the fieldlength again.
Shouldn't a ANALYZE TABLE be done automatically after altering tables?
Every check that we did told us that everything was fine. E.g. CHECK TABLE, SHOW TABLE STATUS, SHOW ERRORS, SHOW WARNINGS. Also mysqlcheck at the database did not throw any suspicous messages.
We have read that under some circumstances the question optimizer could pick/arrange a wrong query based on used table statistics and thus leading to performance issues. As in our case it was not to much traffic at the table and shouldn't have caused such a problem.
Could that be a BUG or is it maybe a known or already solved problem? Maybe we are missing one bit of information.
Here is the ALTER statement for the one table which had this problem when JOINed: