mysql> EXPLAIN SELECT r.rushid FROM rushes r LEFT JOIN browse b ON r.rushID = b.rushID WHERE b.rushID IS NULL AND r.updated < NOW() - INTERVAL 1 DAY; +----+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+ | 1 | SIMPLE | r | ALL | NULL | NULL | NULL | NULL | 784286 | Using where | | 1 | SIMPLE | b | ref | rushid_start | rushid_start | 96 | quentin_v3.r.rushID | 10 | Using where; Using index; Not exists | +----+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+ 2 rows in set (0.00 sec)
MariaDB [quentin_v3]> EXPLAIN SELECT r.rushid FROM rushes r LEFT JOIN browse b ON r.rushID = b.rushID WHERE b.rushID IS NULL AND r.updated < NOW() - INTERVAL 1 DAY; +------+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+ | 1 | SIMPLE | r | ALL | NULL | NULL | NULL | NULL | 784286 | Using where | | 1 | SIMPLE | b | ref | rushid_start | rushid_start | 96 | quentin_v3.r.rushID | 10 | Using where; Using index; Not exists | +------+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+ 2 rows in set (0.001 sec) Look pretty similar. Regards Andy Ling From: Maria-discuss [mailto:maria-discuss-bounces+andy.ling=grassvalley....@lists.launchpad.net] On Behalf Of Gordan Bobic Sent: Tue 02 June 2020 09:44 Cc: Mailing-List mariadb <[email protected]> Subject: Re: [Maria-discuss] [EXTERNAL] Re: Poor performance compared to MySQL Can you post EXPLAIN from both? Is it exactly the same? On Tue, 2 Jun 2020, 09:21 Ling, Andy, <[email protected]<mailto:[email protected]>> wrote: MariaDB is still significantly slower. From: Roberto Spadim [mailto:[email protected]<mailto:[email protected]>] Sent: Mon 01 June 2020 18:33 To: Ling, Andy <[email protected]<mailto:[email protected]>> Cc: Mailing-List mariadb <[email protected]<mailto:[email protected]>> Subject: [EXTERNAL] Re: [Maria-discuss] Poor performance compared to MySQL External Message:Use caution before opening links or attachments what happen when comparing myisam-myisam? Em seg., 1 de jun. de 2020 às 12:51, Ling, Andy <[email protected]<mailto:[email protected]>> escreveu: I am looking at switching from MySQL to MariaDB and have been comparing the performance of the two. I am using the same databases on the same Windows machine and running queries using MySQL and MariaDB and I am finding that MariaDB is 6 times slower. A query that takes 5 seconds on MySQL is taking 28 seconds on MariaDB. I am hoping I have some configuration wrong, so I looking for some help to work out what needs changing. One of the problem queries is a join between two tables. Analyzing the query gives.. ANALYZE FORMAT=JSON SELECT r.rushid FROM rushes r LEFT JOIN browse b ON r.rushID = b.rushID WHERE b.rushID IS NULL AND r.updated < NOW() - INTERVAL 1 DAY; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ | ANALYZE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 107974, "table": { "table_name": "r", "access_type": "ALL", "r_loops": 1, "rows": 784286, "r_rows": 784286, "r_total_time_ms": 245.5, "filtered": 100, "r_filtered": 100, "attached_condition": "r.updated < '2020-05-31 16:35:59'" }, "table": { "table_name": "b", "access_type": "ref", "possible_keys": ["rushid_start", "rushid"], "key": "rushid", "key_length": "96", "used_key_parts": ["rushID"], "ref": ["quentin_v3afp.r.rushID"], "r_loops": 784286, "rows": 10, "r_rows": 1, "r_total_time_ms": 106252, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(b.rushID is null)", "using_index": true, "not_exists": true } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------+ 1 row in set (1 min 48.244 sec) The table has been converted to the Aria engine from MyISAM. The my.ini file has had the following added/changed.. #Not using MyISAM so save memory key_buffer_size=64k #Setting to improve Aria performance aria_pagecache_buffer_size=4007M tmp_table_size=35M max_heap_table_size=35M Thanks for any help. Andy Ling ________________________________ DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You. _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss<https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!7H4WuJBXqeFZECQVfG-JYPJ4jBQuogJuxG49NXvW345dDGFhkJyHmzNG8qfjprUme0Io$> Post to : [email protected]<mailto:[email protected]> Unsubscribe : https://launchpad.net/~maria-discuss<https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!7H4WuJBXqeFZECQVfG-JYPJ4jBQuogJuxG49NXvW345dDGFhkJyHmzNG8qfjprUme0Io$> More help : https://help.launchpad.net/ListHelp<https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmvsU!7H4WuJBXqeFZECQVfG-JYPJ4jBQuogJuxG49NXvW345dDGFhkJyHmzNG8qfjpt4IeMCL$> -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss<https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!-OY7N3hOuVFtlZjCxVQuqUmeXvTFY__pTQgp8SPixHlx84xyuSbWyD2vStQhXKRi-enW$> Post to : [email protected]<mailto:[email protected]> Unsubscribe : https://launchpad.net/~maria-discuss<https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!-OY7N3hOuVFtlZjCxVQuqUmeXvTFY__pTQgp8SPixHlx84xyuSbWyD2vStQhXKRi-enW$> More help : https://help.launchpad.net/ListHelp<https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmvsU!-OY7N3hOuVFtlZjCxVQuqUmeXvTFY__pTQgp8SPixHlx84xyuSbWyD2vStQhXOKqY4e_$> ********************************************************************** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this message, you may not copy, use or deliver this message to anyone. In such event, you should destroy the message and kindly notify the sender by reply e-mail. It is understood that opinions or conclusions that do not relate to the official business of the company are neither given nor endorsed by the company. Thank You.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
