Possible reasons for a slow MySQL / MariaDB query in some table conditions

Question

Possible reasons for a slow MySQL / MariaDB query in some table conditions

I have a strange problem with slow query under certain conditions: I did several tests and was able to isolate the problem in one table with the name products_description(all tables are MyISAM). At first, I noticed that when this table is new (i.e., just imported), the query always runs quickly (~ 0.3 s). However, if I perform any of these operations in this particular table, at any time (even immediately after importing): CHECK, OPTIMIZE, ANALYZEor REPAIR, the request suddenly slows x10 times (it takes ~ 4.5 s) and continues to be on low level.

Please note that I have forcibly denied caching when the query is run to ensure the results are correct.

I can restore performance only when I perform any of these operations in this table:

1) DROPand import it again.

or

2) ALTERany of the following from this table: Collationeither CHECKSUMor DELAY_KEY_WRITE. Then it quickly works with the changed value and, returning to the old value, performance remains fast. Alternatively, ALTER products_description FORCEmay be performed to restore performance.

Even if I'm doing any of the operations CHECK, OPTIMIZE, ANALYZEor REPAIRin the table, query speed drops as long as I do not do either 1) or 2)

, : - (products_description.frm, products_description.MYD, products_description.MYI), , . CHECK , , 10 , 3 , , .

zip ~ 5mb (~ 80mb unzipped). - , , . , MariaDB 10.1+, MySQL 5.6 +.

SQL-, , :

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

EXPLAIN EXTENDED. , - , , , .

:

?

EDIT: , SHOW TABLES:

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 ;

+4

database mysql mariadb database-performance

CreativeMind 19 . '16 19:48

3

. ,

MariaDB [mysql]> set profiling=on;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select date('2010/01/01') + interval (seq * 30) Minute as mydate
                    from seq_0_to_10;
+---------------------+
| mydate              |
+---------------------+
| 2010-01-01 00:00:00 |
| 2010-01-01 00:30:00 |
| 2010-01-01 01:00:00 |
| 2010-01-01 01:30:00 |
| 2010-01-01 02:00:00 |
| 2010-01-01 02:30:00 |
| 2010-01-01 03:00:00 |
| 2010-01-01 03:30:00 |
| 2010-01-01 04:00:00 |
| 2010-01-01 04:30:00 |
| 2010-01-01 05:00:00 |
+---------------------+
11 rows in set (0.00 sec)

MariaDB [mysql]> show profile all;
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| Status               | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file   | Source_line |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| starting             | 0.000061 | 0.000054 |   0.000007 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL          |        NULL |
| checking permissions | 0.000006 | 0.000004 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_parse.cc  |        6051 |
| Opening tables       | 0.000013 | 0.000012 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc   |        4509 |
| After opening tables | 0.000003 | 0.000003 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables           | sql_base.cc   |        4747 |
| System lock          | 0.000002 | 0.000001 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc       |         308 |
| Table lock           | 0.000007 | 0.000006 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables     | lock.cc       |         313 |
| init                 | 0.000015 | 0.000014 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_select          | sql_select.cc |        3427 |
| optimizing           | 0.000006 | 0.000005 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize_inner        | sql_select.cc |        1092 |
| statistics           | 0.000010 | 0.000010 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize_inner        | sql_select.cc |        1373 |
| preparing            | 0.000013 | 0.000012 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize_inner        | sql_select.cc |        1398 |
| executing            | 0.000002 | 0.000001 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec_inner            | sql_select.cc |        2551 |
| Sending data         | 0.000022 | 0.000021 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec_inner            | sql_select.cc |        3223 |
| end                  | 0.000003 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_select          | sql_select.cc |        3462 |
| query end            | 0.000003 | 0.000003 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command | sql_parse.cc  |        5687 |
| closing tables       | 0.000002 | 0.000001 |   0.000000 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | close_thread_tables   | sql_base.cc   |         935 |
| Unlocking tables     | 0.000005 | 0.000005 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_unlock_tables   | lock.cc       |         395 |
| freeing items        | 0.000004 | 0.000003 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_parse           | sql_parse.cc  |        7319 |
| updating status      | 0.000012 | 0.000004 |   0.000007 |                 0 |                   0 |            0 |             0 |             1 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc  |        1937 |
| cleaning up          | 0.000002 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc  |        1956 |
+----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
19 rows in set (0.00 sec)

MariaDB [mysql]>

+1

Bernd Buffen 19 . '16 20:43

. , () - , . , , . mariadb, .

.

1) DB-

2) , ""

3) start the DB server and check again

Now you check the same "slow" speed that you should see in your request. missing index

0

Bernd buffen Apr 20 '16 at 5:58

source share

shoosha · Accepted Answer · 2016-04-27T20:38:43+0000

-, MySQL MariaDB / .

( Oracle, ) () MariaDB ( ), ( ). , , , , MariaDB .

Oracle tho, , ...

, , , ALTER table FORCE. !

Possible reasons for a slow MySQL / MariaDB query in some table conditions

More articles: