I just noticed something very strange. I had a query that was running quite slow on my database (MariaDB 10.1).

When I copied the same exact database to a new database and ran the same query on that database, it took 10% of the time. Both databases are supposedly identical and on the same SSD drive array.

Note that I forced no caching, I made sure the original database is optimized repaired. What can be the possible causes for this huge discrepancy and how can they be verified and fixed?

EDIT: The databases are using the MyISAM engine.

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:

EDIT 2:

I have made a few more tests and was able to isolate the issue to one single table called products_description. When this table is copied from the original database to the copied database, the query always performs fast. However, if I perform any of these operations on this specific table, at any time: CHECK, OPTIMIZE, ANALYZE or REPAIR, the query suddenly slows down x10 times.

I am able to restore the performance only if I am then performing any of these operations:

1. DROP the table and copy it again from the old database.

or

2. ALTER any of the following of that table: Collation or CHECKSUM or DELAY_KEY_WRITE. Tested with the altered values 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, the speed drops until I do either 1) or 2)

One more thing I tested: After I copied the table from the old db to the new, before performing any operation on it, 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.

EDIT 3:

I have compressed the database in a ~5mb zip file (~80mb unzipped). If anyone wants to test the database in your own environment, please let me know and I will send you a download link. I could reproduce this on several different servers, on both MariaDB 10.1+ and MySQL 5.6+.

This the SQL query that I am running and you should test with is:

SELECT DISTINCT pav.products_options_values_id, 
            pav.products_options_values_name, 
            pav.products_options_values_sort_order 
FROM   products_stock ps, 
       products_options_values pav, 
       (SELECT DISTINCT pa.products_id 
        FROM   products_attributes pa, 
               products_options_values pov, 
               (SELECT p.products_id, 
                       p.products_image, 
                       p.products_subimage1, 
                       pd.products_name, 
                       p.products_quantity, 
                       p.products_model, 
                       p.products_ordered, 
                       p.products_price, 
                       p.products_date_added, 
                       p.products_weight, 
                       p.products_length, 
                       p.products_width, 
                       p.products_height, 
                       p.products_tax_class_id, 
                       p.products_status, 
                       IF(s.status, s.specials_new_products_price, NULL) 
                       AS 
                       specials_new_products_price, 
                       IF(s.status, s.specials_new_products_price, 
                       p.products_price) AS 
                       final_price, 
                       IF(p.clearance_price < p.products_cost * 2.25, 
                       p.clearance_price, 
                       p.products_cost * 2.25) 
                       AS 
                       sorting_price 
                FROM   products p 
                       LEFT JOIN specials s 
                              ON p.products_id = s.products_id 
                       LEFT JOIN products_description pd 
                              ON p.products_id = pd.products_id 
                WHERE 
                 /*FASTIDS*/ 
                 p.products_status = '1' 
                 AND Date_sub('2016-04-19', INTERVAL 7000 day) <= 
                     p.products_date_added 
               ) m 
        WHERE  m.products_id = pa.products_id 
               AND pa.options_id = 1 
               AND pa.options_values_id = pov.products_options_values_id 
               AND pov.language_id = '1') q 
WHERE  q.products_id = ps.products_id 
       AND ps.products_stock_attributes = 
           Concat('1-', pav.products_options_values_id) 
       AND ps.products_stock_quantity > 0 
ORDER  BY pav.products_options_values_sort_order ASC 

P.S. I posted this question on stackexchange and was advised to ask the experts here.