Why does ALTER TABLE report no records on MySQL?

Question

I am trying to investigate the cause of a slow query in MySQL; I posted about the query itself previously:How to investigate and optimise a slow MySQL query?

However, I'm asking this question separately because it potentially relates to a different problem that may underlie the first, and I'd be grateful for your insights, please.

I sent the problem referred to above to a colleague who runs a major University's website that is also based on Drupal, which I'm using. He executed the query on their server using a snapshot of our database and, compared with the tens of seconds it took my system, they were getting results in fractions of a second. For the record, our server is well-powered; we're not running a site on a Raspberry Pi or something!

He then did an explain on the query on his server and found that their set up is handling the query quite differently to ours.

This led him to question the integrity of the index in my table, because:

On his server:

mysql> alter table taxonomy_term_data ENGINE=InnoDB; Query OK, 11589 rows affected (0.26 sec) Records: 11589 Duplicates: 0 Warnings: 0

On ours:

mysql> alter table taxonomy_term_data ENGINE=InnoDB; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0

The above was entered to see if we could regenerate the index, on the off-chance that it is corrupt.

I've since tried dumping the table and then re-importing it, as per his suggestion, but that has not made any difference.

I must admit that I am completely out of my depth, and my colleague is unsure why the ALTER table operation, on our production server, is returning 0 records compared with the correct 11,000+ he sees on their test server, despite importing a dump of our db.

Any advice that anyone can offer me to investigate this would be truly helpful.

Many thanks.

Answer 1

I have solved this problem and here is the (very simple) solution, just in case anyone else is struggling with it.

I also gratefully acknowledge the help of Andy Batey at Cambridge University who spotted the issue and helped me.

The clue was that they are running MySQL 5.5; we are on 5.7. On 5.5 the query is fast; on 5.7 it's dreadfully slow.

This led to this thread: query extremely slow after migration to mysql 5.7

For me, the same query executed on the two different platforms was taking a fraction of a second (v5.5) vs 12s (v5.7).

Adding:

"optimizer_switch='derived_merge=off'"

...to my.cnf resulted in the same query now taking 0.05s on v5.7.

I hope this helps anyone else who runs into this upgrade issue. I had spent days trawling fora and groups asking for help and also trawling through the query itself to try to optimise it....