I frequently have to restore our website's production DB to our test server.
When trying to access the restored copy of production, the performance is AWFUL. For example, one particularly heavy web page's worth of queries typically takes 8 seconds to return the results. After restoring from production, this easily takes upwards for 5 minutes.
This can all be rectified by running
mysqlcheck -u myuser -p -o my_db_name
The results look something like this:
my_db_name.table1 note : Table does not support optimize, doing recreate + analyze instead status : OK my_db_name.table2 note : Table does not support optimize, doing recreate + analyze instead status : OK etc...
Why is this slow performance happening in the first place? I haven't seen much in terms of this issue when doing searches online.
I have never experienced this before with either MySQL or MSSQL.
I have a solution (to run
mysqlcheck, or as others have pointed out, just the
analyze portion of that), but it doesn't seem like the "right" way to solve it - I'd like to understand the underlying issue.