-1

I recently moved the database from mysql to mariadb, but the same query is executed in mysql in less than a second, but it takes 20 minutes in mariadb.

query :

SELECT km.beleg, km.art, ke.id as KID 
FROM buchhaltung.kosten_ersatz ke 
LEFT JOIN buchhaltung.konto_match km ON km.beleg = ke.id AND km.art IN (2,12,14,15,16) 
WHERE km.beleg IS NULL

`

show create table on mariadb (table kosten_ersatz):

enter image description here

show create table on mariadb (table konto_match):

enter image description here

2
  • 2
    please show us the EXPLAIN SELECT.. of your query and please no images of code or data
    – nbk
    Mar 10 at 22:21
  • Hi, and welcome to dba.se! Please note that you should not post images of text for the reasons outlined here. Please use formatted text - widget above edit box. You appear to have put MariaDB twice, instead of MariaDB for one SHOW CREATE TABLE and MySQL for the other?
    – Vérace
    Mar 12 at 6:55

1 Answer 1

1

Replace KEY beleg with

INDEX(beleg,  art)

Get rid of the LEFT JOIN, it will have no impact on the query results. (I would expect MariaDB to do that automatically, but your evidence says the opposite.)

This may be the main problem:

ON km.beleg = ke.id

Note that one is a VARCHAR, the other is INT. Not good for JOINing, as the EXPLAIN might hint at.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.