Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

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

klaus.kirchhoff's profile image klaus.kirchhoff posted 3 months ago in General Permalink

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.

elpa's profile image elpa posted 3 months ago Permalink

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...

elpa's profile image elpa posted 3 months ago Permalink

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

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 3 months ago Permalink

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

klaus.kirchhoff's profile image klaus.kirchhoff posted 3 months ago Permalink

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

Code modification/commit 7fbf88b from Ansgar Becker <anse@heidisql.com>, 3 months ago, revision 5982
Optimize SQL query in TDBConnection.GetTableForeignKeys. See https://www.heidisql.com/forum.php?t=36212
ansgar's profile image ansgar posted 3 months ago Permalink

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.

klaus.kirchhoff's profile image klaus.kirchhoff posted 3 months ago Permalink

The fix works fine! Thanks!

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

ansgar's profile image ansgar posted 3 months ago Permalink

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

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.