Status: Closed (View Workflow)
Affects Version/s: 5.5, 10.1, 10.0
Environment:WHM on CentOS 6.7 x64, XAMPP 3.2.2 on Windows 7 x64, WAMP 2.4 on Windows 7 x64
I am having a strange issue with a slow query under certain conditions:
I have made a few tests and was able to isolate the issue to one single table called `products_description` (all the tables are MyISAM).
At first I noticed that when this table is new (ie. just imported), the query always performs fast (~0.3s).
However, if I perform any of these operations on this specific table, at any time (even immediately after importing it):
`CHECK`, `OPTIMIZE`, `ANALYZE` or `REPAIR`, the query suddenly slows down x10 times (takes ~4.5s) and keeps staying always slow.
Note that I forced no-caching when running the query to ensure the results are correct.
I am able to restore the performance only if I am then performing any of these operations on that table:
1) `DROP` the table and import it again.
2) `ALTER` any of the following of that table: `Collation` or `CHECKSUM` or `DELAY_KEY_WRITE`. It then runs fast with the altered value and when reverting back to the old value, the performance remains fast.
Even then, if I perform any of the `CHECK`, `OPTIMIZE`, `ANALYZE` or `REPAIR` operations on that table, the query speed drops until I do either 1) or 2)
One more thing I tested:
Before performing any operation on the table, I backed up the table's files (`products_description.frm`, `products_description.MYD`, `products_description.MYI`), ran the query, it ran fast. Then I performed `CHECK` on the table, ran the query, speed was x10 times slower, I copied the backed up files and overwrote the 3 files, ran the query again, slow speed again.
I have compressed the database in a ~5mb zip file (~80mb unzipped) and uploaded it to the private folder on MariaDB ftp.
Here are the EXPLAIN EXTENDED results. Seems like the optimizer is working differently on both tables, but that doesn't really explain why that happens, as the copied database is supposedly identical.
These are the screenshots of the profiling of the query on the slow and fast database:
This the SQL query that I am running and you should test with is: