#700 Slow query select in table

[Marcos Gómez Buceta]

With the version change to 4.7.2, I have noticed the data loads of the tables in a database, it takes about 50% more time to display the data, than before.
I am talking about a table of few records (29).
In fact, I have returned to version 4.7.1, because it becomes exhausting to review the data in these circumstances.
Any solution?.
Thanks.

[Peter Liscovius]

Which database type? Is it with all database types or just one database type?
How many tables has your database? What types are in your table? Which Adminer plugins are enabled (blob/image viewing plugins) ?
Does the problem exists also with the github dev version (multiple files, no jsshrink minification or jush)? I use only the github sources and no speed problem with a few tables aside from some Firefox warnings in the developer console.

[Marcos Gómez Buceta]

The type of database used is MySQL, and it happens to me with all the databases I have about MySQL. I did not try with others.
The tables on which I did the tests have numerical indexes, and the data types are integers and varchar.
I do not have any type of add-ons enabled, and the download source is the Adminer page itself.

Thanks for your attention.

[Martin Štěpař]

I have same issue. Some additional info:

• localhost is OK, server in same network has issue
• some tables are OK, some have issue
• issue affects "Show structure" too, not only data browsing (I guess name of this section in English, I use Adminer in another language)
• version 4.7.1 works well
• no additional add-ons installed

[Duncan Fairley]

This is happening for us now. But taking minutes and minutes to load.

Query that was running for 300 seconds when trying to "Select data" a table:

SELECT COLUMN_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = 'special_safety_supervision_ibfk_1' ORDER BY ORDINAL_POSITION

Downgraded to 4.7.1 and is great again.

[Peter Liscovius]

Questions: Which Mysql versions? Which table types? How many tables, how many relations? What are the Mysql vars? See something like http://example.com/adminer/?username=YOURUSERNAME&variables=

On what distribution does it run? No issue here with fresh vagrant installs of Debian10(MariaDB-10.3.15) or Opensuse15.1(MariaDB-10.2.22)

When you search the internet for 'slow information_schema query' some interesting topics on stackoverflow pop up.

Probably related commits between 4.7.1 and 4.7.2:
https://github.com/vrana/adminer/commit/36ade4e1974714c4042f313bae69ad3847764bcf
https://github.com/vrana/adminer/commit/d5bf51b2a7e5c818b33330447d106b2e96cb9744

I made a previous pull request that does not loop queries and instead uses a JOIN:
https://github.com/vrana/adminer/pull/348/files

but probably does not solve the slowiness issue either.

As this seems a problem on the Mysql server side, what can be tried to resolve the problem:

• Identify which versions of Mysql and which configuration option of Mysql have the slow INFORMATION_SCHEMA query problem.
• Tweak Mysql or Mariadb configuration to speed up information_schema queries.

Here on my local debian10 install the default install shows: innodb_stats_on_metadata OFF
I don't know it is related to the problem described here, but on my local virtualbox box it is only around 20ms per information_schema.KEY_COLUMN_USAGE query (I use my Adminer github playground fork for that)

Ideas on Adminer's side:

• When it is known the info is requested for a Mysql table type that does not support foreign key constraints (e.g. MyISAM table type), there is no need:
  a) either for calling Adminer's foreign_keys() function
  or
  b) Adminer's foreign_keys() functions itself does only the INFORMATION_SCHEMA query if the table type supports it.
• The server side configuration related to Mysql could be read. If it is bad for reading information_schema stuff, an info alert (like the error or success alerts in Adminer) could show the user some hints.

[Jakub Vrána]

I believe that 67b84d11 should fix this. Please confirm.

[Martin Štěpař]

Perfect, thanks!

[Duncan Fairley]

Appears fixed for me. Thanks.

[Marcos Gómez Buceta]

Thanks!!!.