Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5, 10.0, 10.1
-
WHM on CentOS 6.7 x64, XAMPP 3.2.2 on Windows 7 x64, WAMP 2.4 on Windows 7 x64
-
10.2.13, 10.3.6-1
Description
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.
or
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.
http://i.stack.imgur.com/NQ9YQ.png
http://i.stack.imgur.com/pLyOt.png
These are the screenshots of the profiling of the query on the slow and fast database:
http://i.stack.imgur.com/JBlwP.png
http://i.stack.imgur.com/E1qf0.png
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
|