When changing tables from the sidebar an SQL query is running quite slow on MariaDB 11.X

Root cause is this (sample) query:

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA='crs_test' AND TABLE_NAME='COMMODITY' AND REFERENCED_TABLE_NAME IS NOT NULL;

Returns 4 Rows and takes 13,276 Sec. On other Tables the query takes up to 30 sec.

A export of an "empty" DB with about 150 tables and all in all 15000 rows (8MB) runs nearly 1h.

We are using a MariaDB 10.1.12 instance with several databases (> 100) of the same structure.

Changing "innodb_stats_on_metadata" has no effect.

We went back to Heidi version 10.3.0.5771 - this works fine.

I can confirm this. In my case I had the same situation and the query took brutally long so I had to kill it on the server side. Heidy was frozen in the meantime... I'm not happy to see Heidy to send queries to large information schemas...

This was my query:

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA='system' AND TABLE_NAME='tags' AND REFERENCED_TABLE_NAME IS NOT NULL

Then feel free to post alternative queries how to get required details about foreign keys of a table.

Ansgar, usualy posts like your are not helpfull. In this case I have a surprising simple alternative.

Change from CONSTRAINT_SCHEMA column to TABLE_SCHEMA. I think in the deep of MySQL / Maria this causes less joins.

This speeds up the select to nearly zero time...

SELECT *

FROM information_schema.KEY_COLUMN_USAGE

WHERE 1=1

//AND CONSTRAINT_SCHEMA='crs_test'

AND TABLE_SCHEMA='crs_test'

AND TABLE_NAME='DEAL'

AND REFERENCED_TABLE_NAME IS NOT NULL;

Be aware that using CONSTRAINT_SCHEMA once causes the information beeing in the cache. Pls. use a different table for each test.

Cheers, Klaus

Oh I was hoping one or two of my 8463 posts were quite helpful...

Anyway, I just modified the query as you suggested. Please update to the next build in a few minutes and retry.

The fix works fine! Thanks!

But "Issue #1028: Code cosmetic in TDBObject.GetTableColumns" seems a bit too harmless :-)

That was a different commit. See the one above my previous comment.