title: Possible causes that a MySQL/MariaDB query is slow under some table conditions
body: <p>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 <code>products_description</code> (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):
  <code>CHECK</code>, <code>OPTIMIZE</code>, <code>ANALYZE</code> or <code>REPAIR</code>, the query suddenly slows down x10 times (takes ~4.5s) and keeps staying always slow.</p>
  
  <p>Note that I forced no-caching when running the query to ensure the results are correct.</p>
  
  <p>I am able to restore the performance only if I am then performing any of these operations on that table:</p>
  
  <p>1) <code>DROP</code> the table and import it again.</p>
  
  <p>or</p>
  
  <p>2) <code>ALTER</code> any of the following of that table: <code>Collation</code> or <code>CHECKSUM</code> or <code>DELAY_KEY_WRITE</code>. It then runs fast with the altered value and when reverting back to the old value, the performance remains fast.
  Alternatively <code>ALTER products_description FORCE</code> can be executed to restore performance.</p>
  
  <p>Even then, if I perform any of the <code>CHECK</code>, <code>OPTIMIZE</code>, <code>ANALYZE</code> or <code>REPAIR</code> operations on that table, the query speed drops until I do either 1) or 2)</p>
  
  <p>One more thing I tested:
  Before performing any operation on the table, I backed up the table's files (<code>products_description.frm</code>, <code>products_description.MYD</code>, <code>products_description.MYI</code>), ran the query, it ran fast. Then I performed <code>CHECK</code> 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.</p>
  
  <p>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+.</p>
  
  <p>This is the SQL query that I am running and you should test with:</p>
  
  <pre><code>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 &lt; 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) &lt;= 
                       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 &gt; 0 
  ORDER  BY pav.products_options_values_sort_order ASC 
  </code></pre>
  
  <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/NQ9YQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NQ9YQ.png" alt="Slow database&#39;s EXPLAIN"></a></p>
  
  <p><a href="https://i.stack.imgur.com/pLyOt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pLyOt.png" alt="Fast database&#39;s EXPLAIN"></a></p>
  
  <p>These are the screenshots of the profiling of the query on the slow and fast database:</p>
  
  <p><a href="https://i.stack.imgur.com/JBlwP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JBlwP.png" alt="Slow database profiling"></a>
  <a href="https://i.stack.imgur.com/E1qf0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E1qf0.png" alt="Fast database profiling"></a></p>
  
  <p>What can be the possible causes for this huge discrepancy and how can they be verified and fixed?</p>
  
  <p><strong>EDIT:</strong>
  As requested in the comments, these are the SHOW TABLES:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `products` (
    `products_id` int(11) NOT NULL AUTO_INCREMENT,
    `products_quantity` int(4) NOT NULL DEFAULT '0',
    `products_model` bigint(20) DEFAULT NULL,
    `products_image` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_med` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_lrg` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_sm_1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_xl_1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_sm_2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_xl_2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_sm_3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_xl_3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_sm_4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_xl_4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_sm_5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_xl_5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_sm_6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_image_xl_6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_bimage` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_subimage1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_bsubimage1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_subimage2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_bsubimage2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_subimage3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_bsubimage3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_subimage4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_bsubimage4` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_subimage5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_bsubimage5` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_subimage6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_bsubimage6` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `clearance_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_cost` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_rmb_cost` decimal(7,2) DEFAULT NULL,
    `products_best_rmb` decimal(7,2) DEFAULT NULL,
    `products_thb_flag` tinyint(1) NOT NULL DEFAULT '0',
    `products_date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    `products_last_modified` datetime DEFAULT NULL,
    `products_date_available` datetime DEFAULT NULL,
    `products_weight` decimal(5,2) NOT NULL DEFAULT '0.00',
    `products_length` decimal(5,2) NOT NULL DEFAULT '0.00',
    `products_width` decimal(5,2) DEFAULT '0.00',
    `products_height` decimal(5,2) DEFAULT '0.00',
    `products_status` tinyint(1) NOT NULL DEFAULT '0',
    `products_tax_class_id` int(11) NOT NULL DEFAULT '0',
    `manufacturers_id` int(11) DEFAULT NULL,
    `products_ordered` int(11) NOT NULL DEFAULT '0',
    `products_parent_id` int(11) NOT NULL DEFAULT '0',
    `products_price1` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price2` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price3` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price4` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price5` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price6` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price7` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price8` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price9` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price10` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price11` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price1_qty` int(11) NOT NULL DEFAULT '0',
    `products_price2_qty` int(11) NOT NULL DEFAULT '0',
    `products_price3_qty` int(11) NOT NULL DEFAULT '0',
    `products_price4_qty` int(11) NOT NULL DEFAULT '0',
    `products_price5_qty` int(11) NOT NULL DEFAULT '0',
    `products_price6_qty` int(11) NOT NULL DEFAULT '0',
    `products_price7_qty` int(11) NOT NULL DEFAULT '0',
    `products_price8_qty` int(11) NOT NULL DEFAULT '0',
    `products_price9_qty` int(11) NOT NULL DEFAULT '0',
    `products_price10_qty` int(11) NOT NULL DEFAULT '0',
    `products_price11_qty` int(11) NOT NULL DEFAULT '0',
    `products_qty_blocks` int(11) NOT NULL DEFAULT '1',
    `products_group_access` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'G,0',
    `products_nav_access` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'G,0',
    `sort_order` smallint(3) NOT NULL DEFAULT '0',
    `vendors_id` int(11) DEFAULT '1',
    `vendors_product_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `vendors_prod_id` varchar(24) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `vendors_prod_comments` mediumtext COLLATE utf8_unicode_ci,
    `products_qty_days` smallint(4) NOT NULL DEFAULT '0',
    `products_qty_years` smallint(4) NOT NULL DEFAULT '0',
    `products_quantity_order_min` int(8) NOT NULL DEFAULT '1',
    `products_quantity_order_units` int(8) NOT NULL DEFAULT '1',
    `products_price_list` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_price_rebate` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_discount1` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_discount2` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_discount3` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_discount4` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `products_discount1_qty` int(6) NOT NULL DEFAULT '0',
    `products_discount2_qty` int(6) NOT NULL DEFAULT '0',
    `products_discount3_qty` int(6) NOT NULL DEFAULT '0',
    `products_discount4_qty` int(6) NOT NULL DEFAULT '0',
    `products_discounts_id` int(11) NOT NULL DEFAULT '0',
    `products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0',
    `product_is_free` tinyint(1) NOT NULL DEFAULT '0',
    `product_is_call` tinyint(1) NOT NULL DEFAULT '0',
    `products_quantity_mixed` tinyint(1) NOT NULL DEFAULT '0',
    `product_is_showroom_only` tinyint(1) NOT NULL DEFAULT '0',
    `products_discount_percentage` tinyint(1) NOT NULL DEFAULT '0',
    `products_price_excluded` tinyint(1) NOT NULL DEFAULT '0',
    `products_showhide` tinyint(1) NOT NULL DEFAULT '1',
    `products_group` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_vendor_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_comments` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_customers_approved` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`products_id`),
    KEY `idx_products_date_added` (`products_date_added`),
    KEY `products_model` (`products_model`),
    KEY `idx_products_customers_approved` (`products_customers_approved`),
    KEY `idx_products_status` (`products_status`),
    KEY `idx_products_price` (`products_price`),
    KEY `products_thb_flag` (`products_thb_flag`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97489 ;
  
  CREATE TABLE IF NOT EXISTS `products_attributes` (
    `products_attributes_id` int(11) NOT NULL AUTO_INCREMENT,
    `products_id` int(11) NOT NULL DEFAULT '0',
    `options_id` int(11) NOT NULL DEFAULT '0',
    `options_values_id` int(11) NOT NULL DEFAULT '0',
    `options_values_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `price_prefix` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `products_options_sort_order` smallint(3) unsigned NOT NULL DEFAULT '9999',
    `product_attribute_is_free` tinyint(1) NOT NULL DEFAULT '0',
    `products_attributes_weight` decimal(8,4) NOT NULL DEFAULT '0.0000',
    `products_attributes_weight_prefix` char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `attributes_price_onetime` decimal(15,4) NOT NULL DEFAULT '0.0000',
    `attributes_display_only` tinyint(1) NOT NULL DEFAULT '0',
    `attributes_default` tinyint(1) NOT NULL DEFAULT '0',
    `attributes_qty_prices_onetime` mediumtext COLLATE utf8_unicode_ci,
    `attributes_discounted` tinyint(1) NOT NULL DEFAULT '1',
    `attributes_price_factor` decimal(8,2) NOT NULL DEFAULT '0.00',
    `attributes_price_factor_offset` decimal(8,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`products_attributes_id`),
    KEY `idx_products_attributes_products_id` (`products_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=57106 ;
  
  CREATE TABLE IF NOT EXISTS `products_description` (
    `products_id` int(11) NOT NULL AUTO_INCREMENT,
    `language_id` int(11) NOT NULL DEFAULT '1',
    `products_name` varchar(160) COLLATE utf8_unicode_ci NOT NULL,
    `products_blurb` mediumtext COLLATE utf8_unicode_ci,
    `products_description` mediumtext COLLATE utf8_unicode_ci,
    `products_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_viewed` int(5) DEFAULT '0',
    `products_head_title_tag` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL,
    `products_head_desc_tag` longtext COLLATE utf8_unicode_ci,
    `products_head_keywords_tag` longtext COLLATE utf8_unicode_ci,
    `products_seo_url` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    PRIMARY KEY (`products_id`,`language_id`),
    KEY `products_name` (`products_name`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97489 ;
  
  CREATE TABLE IF NOT EXISTS `products_options_values` (
    `products_options_values_id` int(11) NOT NULL DEFAULT '0',
    `language_id` int(11) NOT NULL DEFAULT '1',
    `products_options_values_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `products_options_values_sort_order` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`products_options_values_id`,`language_id`),
    KEY `products_options_values` (`products_options_values_sort_order`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  
  CREATE TABLE IF NOT EXISTS `products_stock` (
    `products_stock_id` int(11) NOT NULL AUTO_INCREMENT,
    `products_id` int(11) NOT NULL DEFAULT '0',
    `products_stock_attributes` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
    `products_stock_quantity` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`products_stock_id`),
    UNIQUE KEY `idx_products_stock_attributes` (`products_id`,`products_stock_attributes`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=57375 ;
  </code></pre>