When changing tables from the sidebar an SQL query is running quite slow on MariaDB 10.1.41

I have update to the latest (10.3.0.5813) and noticed that there is a new query running every time I change the selected table or view

SELECT k.*, r.UPDATE_RULE, r.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE AS k, information_schema.REFERENTIAL_CONSTRAINTS AS r WHERE k.CONSTRAINT_SCHEMA='local_database' AND k.TABLE_NAME='local_table' AND k.REFERENCED_TABLE_NAME IS NOT NULL AND k.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA AND k.TABLE_NAME=r.TABLE_NAME AND k.CONSTRAINT_NAME=r.CONSTRAINT_NAME;

This query takes up to 5 seconds to execute on my VM and during this time HeidiSQL freezes until it receives reply from the sql query . HeidiSQL is running on Windows 7

I installed previous version (10.3.0.5771) I see the above query is not executed for every new table you select and there is no problem.

This is Heidi's new way to retrieve foreign key details on MySQL/MariaDB/MSSQL/PG. Although I must say this is work in progress, I wonder why it is so slow for you.

If you want to help you could try to find a quicker SQL query for getting the name of a foreign key, besides referenced table and columns.

Also, you could try if this one with minimum selected fields is quicker:

SELECT
  k.CONSTRAINT_NAME, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.COLUMN_NAME, k.REFERENCED_COLUMN_NAME,
  r.UPDATE_RULE, r.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE AS k, information_schema.REFERENTIAL_CONSTRAINTS AS r
WHERE
  k.CONSTRAINT_SCHEMA='local_database'
  AND k.TABLE_NAME='local_table'
  AND k.REFERENCED_TABLE_NAME IS NOT NULL
  AND k.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA
  AND k.TABLE_NAME=r.TABLE_NAME
  AND k.CONSTRAINT_NAME=r.CONSTRAINT_NAME;

Tables in the database I was working were 'MyISAM', I think there is no foreign key all together. I'll upgrade again tomorrow and check it.

This is the same for me When switching from one table to another. It always takes 15s. With this feature it's impossible to work with your beautiful HeidiSQL. So it downgraded to version 10.2. I work with Windows 10 + WAMP 3.1.7 (MySQL 5.7.24)

SELECT k.CONSTRAINT_NAME, k.REFERENCED_TABLE_SCHEMA, k.REFERENCED_TABLE_NAME, k.COLUMN_NAME, k.REFERENCED_COLUMN_NAME, r.UPDATE_RULE, r.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE AS k, information_schema.REFERENTIAL_CONSTRAINTS AS r WHERE k.CONSTRAINT_SCHEMA='local_database' AND k.TABLE_NAME='local_table' AND k.REFERENCED_TABLE_NAME IS NOT NULL AND k.CONSTRAINT_SCHEMA = r.CONSTRAINT_SCHEMA AND k.TABLE_NAME=r.TABLE_NAME AND k.CONSTRAINT_NAME=r.CONSTRAINT_NAME; --> / Betroffene Zeilen: 0 Gefundene Zeilen: 0 Warnungen: 0 Dauer von 1 Abfrage: 14,266 Sek. /

It's also super slow for me. More than 1 minute on a production MySQL database with only innodb tables. It put a small load on the server just running this query and the IU was completly frozen for more than 1 minute.

I also have that same problem: very slow switching between tables in the sidebar.

The same here, it takes about 5-7 seconds.

The above logic is not in a release yet - v10.3 works with the older, quicker logic.

If someone could help to find a quicker query for detecting foreign key that would be helpful.

I just separated the join for foreign key retrieval into two separate queries, which seem to fire faster now.

See issue #852 .

But I need to invent some caching mechanism, as these many queries are fired several times for one table click.

Note this is all belongs to the SQLite implementation in a broad sense.

Another possible solution could be to SET GLOBAL innodb_stats_on_metadata = OFF before running the query, and SET GLOBAL innodb_stats_on_metadata = ON after the query. Starting with MySQL 5.6 the default value of innodb_stats_on_metadata is OFF, but in prior versions the default value is ON.

On my server this improves the speed of query from 5-7 sec to 1.5 sec.

However, it does not improve the speed as much as your solution (separating the join into two separate queries).