Problems with MySQL/MariaDB performance

Entwicklung / Development Fehleranalyse / Troubleshooting
1

Hi there,

@hannes_oberreiter is regularily sharing the development progress of the Btree application at Development Updates info.btree.at – it’s always a pleasure to read your updates, so thanks for sharing!

Maybe there is a chance we can help him out on some details.

Continuing the discussion from Development Updates info.btree.at:

What are the problems you are observing, Hannes? Do you have any more details?

With kind regards,
Andreas.

2

Hi all,

thanks Andreas for generating a new thread. First of all the performance of my queries is drastically dropping if order by statements are used in connection with my VIEWS.

In the thread title you used MariaDB which I was completely unaware of, interestingly enough changing the Database did improve the performance significantly. Now I don’t know if my queries are bad or my MySQL setup. :frowning:

My benchmark results between MySQL and MariaDB (you can also see slow performance of my queries with MySQL and VIEWS).

# MySQL 5.7
use btree_node;
# queens: 0.012 sec
select queens.id as id, queens.name as name from queens order by name desc, id asc;
# queens + hives_locations VIEW: 0.256 sec
select queens.id as id, queens.name as name from queens left join hives_locations as hive_location on hive_location.hive_id = queens.hive_id order by name desc, id asc;
# queens + queens_locations VIEW: 1.608 sec
select queens.id as id, queens.name as name from queens left join queens_locations as queen_location on queen_location.queen_id = queens.id order by name desc, id asc;
# queens + queens_locations VIEW without ORDER BY: 0.658 sec
select queens.id as id, queens.name as name from queens left join queens_locations as queen_location on queen_location.queen_id = queens.id;

# MariaDB 10.7.3
use btree_node;
# queens: 0.0025 sec
select queens.id as id, queens.name as name from queens order by name desc, id asc;
# queens + hives_locations VIEW: 0.0021 sec
select queens.id as id, queens.name as name from queens left join hives_locations as hive_location on hive_location.hive_id = queens.hive_id order by name desc, id asc;
# queens + queens_locations VIEW: 0.0037 sec
select queens.id as id, queens.name as name from queens left join queens_locations as queen_location on queen_location.queen_id = queens.id order by name desc, id asc;
# queens + queens_locations VIEW without ORDER BY: 0.0021 sec
select queens.id as id, queens.name as name from queens left join queens_locations as queen_location on queen_location.queen_id = queens.id;

Cheers
Hannes

3

Dear Hannes,

MySQL has been sold to Sun, then acquired by Oracle. MariaDB is the official successor in the spirit of FOSS. Good that it delivers better performance out of the box, but still we will have to look at the databases indexes, if performance drops on e.g. filtering and sorting.

I am out for hiking until Sunday, we can have a more in-depth look at the database schema next week.

Cheers,
Andreas.

4

Hi Andreas,

thanks for your offer. Solved the problem did completely forgot about index setting. My views did use the date columns which no index was present. Still interesting that MariaDB seems to not care that much about indexes in Views.

(Edit: Upgrading to MySQL 8.X did also resolve the issue instantly)

Have fun hiking and thanks again.
Cheers
Hannes

2 Likes