Here it is, the command was:
RESET QUERY CACHE; FLUSH STATUS; SELECT b.*, p.category_id FROM brands
b JOIN products p USING(brand_id) GROUP BY category_id, brand_id ORDER
BY title ASC; SHOW STATUS LIKE 'Handler%';
5.5
---
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 11051 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 190 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 62730 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 62508 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
10.0
----
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 150 |
| Handler_read_last | 0 |
| Handler_read_next | 62206 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 187 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 339 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 62206 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
I already tried to remove order by and also group by but it didn't
make any difference related to speed.
Citát Sergey Petrunia <ser...@mariadb.com>:
On Thu, Feb 11, 2016 at 03:43:54PM +0100, azu...@pobox.sk wrote:
Hi,
we upgraded MariaDB 5.5 to 10.0 on one server last night and are now
dealing with severe performance issues. Here is one case where it's
notable:
Query - SELECT b.*, p.category_id FROM brands b JOIN products p
USING(brand_id) GROUP BY category_id, brand_id ORDER BY title ASC;
...
Query time on 5.5: about 0.2s
Query time on 10.0: between 2 and 9s (if not cached)
Explain on 5.5: http://watchdog.sk/5.5.png
Explain on 10.0: http://watchdog.sk/10.png
This is weird, query plans look the same. However, p.rows = 376, which means
that on version 10.0 the optimizer expects to read a lot more rows.
Can you run the queries again (on both 5.5 and 10.0) and check how many rows
they actually read? This can be done as follows:
FLUSH STATUS;
<query>;
SHOW STATUS LIKE 'Handler%';
I would also try removing the ORDER BY clause and see if the difference in
query execution time is still there.
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
