Possible causes that a MySQL/MariaDB query is slow under some table conditions

Question

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. Alternatively ALTER products_description FORCE can be executed to restore performance.

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). 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 is the SQL query that I am running and you should test with:

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 

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:

What can be the possible causes for this huge discrepancy and how can they be verified and fixed?

EDIT: As requested in the comments, these are the 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 ;

Enlistment

How much is 10% of the time? – Dingle

@JoeStefanelli Defragmentation of the file system? – Whittier

How did you "copy the database" ? Did you run a msyqldump, and then import that to a different (empty) database on the same server? Note that the file system cache on the OS is going to play a role. I'd run that same query multiple times, and discard the results from the first run. Also, with MyISAM, the query isn't going to start executing until it can obtain an exclusive lock on the table. so some of the execution time could be time waiting to obtain a lock for a DML operation to complete. – Roscoeroscommon

I copied via phpMyAdmin "copy database" option. I ran the queries multiple times at different hours to verify and the results are always the same. Both databases are not in use via any connection. – Whittier

For debugging this, I recommend you run EXPLAIN on the query, against both databases, and compare the results. We'd be interested in the indexes that were considered possible, which indexes were chosen, statistics and expected cardinality, etc.. – Roscoeroscommon

I did that too, the EXPLAIN is indeed showing different results. I will post them here in a moment. – Whittier

Different execution plans often exhibit different performance characteristics when operating on "large" tables and/or intermediate results. (I'm not at all familiar with phpMyAdmin, or what operations the "copy database" option performs.) – Roscoeroscommon

That's the thing. The databases are supposedly identical so there should not be a different execution plan performed by the optimizer. I added screenshots, although I am not sure if that helps resolving this mystery. – Whittier

@CreativeMind - can post us a little more info. The hole query, the create of your tables and if possible some sample data. there are many thing to optimize. the query, the server configuration, the engine or the some optimizer switches – Cythera

My guess: Table statistics, which might be reset by altering the table, and calculated again with CHECK, OPTIMIZE..- I would post that question on dba.stackexchange.com – Gaelan

@PaulSpiegel ok, but if that's the case, why would table statistics affect the query's performance? and so badly? is that a mysql bug? – Whittier

@PaulSpiegel Also - which file/s are those table statistics stored in? Any of the above 3 I mentioned? (MYI, MYD or frm) – Whittier

@CreativeMind, i don't know that much about table statistics. I just know that MySQL uses them to choose the "best" execution plan. But sometimes MySQL ist just wrong. I had issues when MySQL decided to use a really stupid join order (causing a cross join of two big tables), which i had to fix with STRAIGHT_JOIN. I can't tell you much more, but a DBA should be able to. – Gaelan

How much RAM in the machine? – Christensen

Please provide SHOW CREATE TABLE for the tables. – Christensen

@RickJames the database size is ~80mb and machine has 64gb RAM. Would you like to download it? It's only 5mb compressed and you can easily reproduce this issue on your environment in minutes if you follow my steps – Whittier

p.s @RickJames added the SHOW TABLES, but would probably be best if you test the entire db... – Whittier

Yeah, I might try it out. Dropbox? Some other way? – Christensen

I can send you a download link privately. Where can I send it to? – Whittier

Let us continue this discussion in chat. – Christensen

by the way, @RickJames I could reproduce this on windows 7 both on XAMPP 3.2.2 and WAMP 2.4 – Whittier

Answer 1

Apparently you have discovered quite a serious bug in the MySQL/MariaDB engine and /or optimizer.

This bug has been reported (both to Oracle and Maria) and reproduced (confirmed) by MariaDB staff (Elena Stepanova), who already assigned it to one of their lead developers (Sergei Petrunia). I believe it will probably be fixed quite soon, as MariaDB people seem to work quite efficiently.

With Oracle tho, well, a different story entirely...

You have already found temporary workarounds, as you posted, such as running ALTER table FORCE. Good job!

Lardy

Is there any link to that bug report? – Knitting

Answer 2

take a look in the Profile. There you can see where the Engine spend the time

Sample

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]>

Cythera

you directly can see the first big difference on SENDING DATA. Is there a difference in network or connection to the DB ? – Cythera

There is no difference in network or connection to the db, they are both on the same storage and both standalone without any connections to them. – Whittier

Then the my.cnf must be different in some buffer pools. can you compare the sizes – Cythera

in MariaDB 10.1 much performance option are disabled to be compatible to MySQL. is MariaDB running on a linux server ? – Cythera

Let us continue this discussion in chat. – Cythera

Answer 3

i have made some small test. so is it possible that the first (slow) DB is the DB where you created an worked with like insert and delete. Then it look like that the records are not in the same order like the copy. and is there a index missing mariadb must read more time the datafile.

You can test it easy.

1) shutdown the DB-server

2) copy the tablefiles from old to new position, so that they overwrite the "new" one

3) start the DB- server and test again

hase now both test the same "slow" speed you must take a look in your query. there is a Index missing

Cythera

Thanks. I have edited my post and added more, important information. – Whittier

I have rewrote my question and subject. Hope it is clearer now. – Whittier