I am new to MySQL/MariaDB.
I am comparing performance of a Stored Procedure in both MS SQL and MariaDB and found the performance of MariaDB to be slower than MS SQL. I found that the same query is taking more time in MariaDB than MSSQL. In fact i had to add an index on temporary table in the stored procedure which was not required in MS SQL. Why this type of result. How and where can i dig more into the behavior of MariadB engine like execution plan(detailed). Which performance monitoring tool(open source) will help me in handling slow queries as well as optimization other than slow query log.
How can i use EXPLAIN for below code CREATE TEMPORARY TABLE tmp_ABC AS SELECT * from tbl_ABC
Regards, Saumik Vora
Three ways to get execution plan info (in order of detail):
EXPLAIN SELECT ...EXPLAIN FORMAT=JSON SELECT ...EXPLAIN works on some queries other than SELECT. If it does not work for your CREATE, simply apply it to just the SELECT. (In either case, that query will have a very trivial Explain.)
innodb_buffer_pool_size is the most important setting in MariaDB; it is about 70% of available RAM?
MySQL/MariaDB will auto-generate indexes derived tables (FROM ( SELECT ... )) in some cases; but not on temp tables.
There are several techniques for avoiding the need for explicit (CREATE TEMPORARY TABLE ...) temp tables; would you care to show us the query; we may have some tips.
Because of many structural differences between MSSql and MariaDB, there are likely to be some queries that run faster or slower in each.
Provide us with one of your queries that is slower in MariaDB; we may be able to speed it up.