Debian 7 x86_64

So the "request" is erp_view_supplyneeds_global?

What queries are you doing on these view (this is usually bad for performance anyway)?

You haven't included your table structures. SHOW INDEXES FROM

for all table would also help.

Can you include EXPLAIN

for both views? And on the queries you are using?

The larger query might be exceeding query_cache_limit size and therefore isn't cached.

Honestly these queries are horrible and quite hard to read. use language features.
IF(x > y, x-y, 0) is the same as MAX(x-y,0)
if(x,x,0) is same as COALESCE(x,0)

If you can simplify and include them in a readable presentation that would be good too.

I'm full agree with you but I can not change the request, I just host it... And we gone back to Mysql. I report it here because it seems that there is a real bug behind this problem.

The "request" is erp_view_supplyneeds_global. I assume the bug come from the huge amount of SUM(). Just a SELECT * FROM `erp_view_supplyneeds_global` WHERE `product_id` = 1; took 30 sec, instead of 0,5 sec on Mysql.

To help, I will try next week to reproduce the problem on a clean DB and send it to you.

A comparative compare of EXPLAIN request_query on mariadb and mysql (which version?) would help.

To help, I will try next week to reproduce the problem on a clean DB and send it to you.

Thank you.

private uploads are there if required: https://mariadb.com/kb/en/meta/ftp/

There was no feedback for a month, I'm closing it.
Feel free to add a comment or reopen the issue if you have more information to provide.

I have the same issue (on CentOS 7).

It occurs both with query cache enabled (stays in "saving in query cache") and disabled (stays in "copying to tmp table").

Both MariaDB 10.1.19 and MySQL 5.7.16 are running with the same config.

Here is a comparative compare of EXPLAIN. I have also uploaded the full db schema to the private ftp.

MDEV-8076-MariaDB-10.1.19.txt MDEV-8076-MySQL-5.7.16.txt

Thanks for the explains. I can see the first query is significantly slower on MariaDB. Is there much difference in the wall clock time for the second and third query?

In the mean time MRR settings may help: https://mariadb.com/kb/en/mariadb/multi-range-read-optimization/

These can be set on a session basis for testing.

The supplier of these schemas should be guided to add primary keys indexes to their tables to speed up even the MySQL faster varient.

Queries:
1: generated by the application, takes >25s
2: the "erp_view_supplyneeds_global" view, takes 25s
3: the "erp_view_supplyneeds_base" view, takes <1s

There is zero wall clock time between all of the queries, I have executed them one by one.

anybodywise,
do you still have this issue with performance?
If yes, please provide the output of

SHOW global variables\G

We encountered the exact same problem, with the exact same view. The best solution is to update BoostMyShop to 2.9.7 or above, because this version removes the problematic views (search for "Maria DB" in http://blogmyshop.com/embedded-erp-2-9-7-is-released-740.html).

But we find a workaround by lowering join_buffer_size. Here are the different query times :

• With join_buffer_size = 1M and lower : 0.18s after restart, then 0s thanks to cache.
• With join_buffer_size = 2M: 15s after restart, then 15s again, no cache.
• With join_buffer_size = 4M: 21s after restart, then 21s again, no cache.
• With join_buffer_size = 8M or more : 19s after restart, then 19s again, no cache.