#4858 (ok 4.4.7) issues with SQL autocomplete

[Alexander Schuch]

I use the SQL query editor quite often. The SQL autocomplete is a nice feature. However, it seems to be quite slow. I attached a screenshot from Firefox developer view.

It seems as if HTTP requests are fired very often and only shortly one after another. I hit "submit query" and it takes ages for the query to return. Once it returns, it says that the query only took about 0.03 seconds, even though I had to wait for about a minute or so.

I have many databases and tables. Maybe querying INFORMATION_SCHEME is just slow?

My only idea to circumvent the problem is to not "stack" autocomplete requests. That is, only fire a request if there is non currently "in the works". This maybe would make the autocomplete feel less "responsive", but at least it would make the overall experience much more "responsive".

[Madhura Jayaratne]

Looks like this functionality already uses SHOW TABLES/COLUMNS/INDEXES instead of querying INFORMATION_SCHEMA. Btw, how many tables do you have in this database?

[Alexander Schuch]

I have about 120 tables in that database.

[Alexander Schuch]

I played around a bit more and to me, he bug looks like this:

• If a key is pressed, and no autocomplete information is available, fetch autocomplate information.
• If a key is pressed, and autocomplete information is available, suggest autocomplete, but do not fetch anymore.

This leads to the problem that as long as the first autocomplete information is not yet succcessfully fetched, every further keystroke leads to a further request.

So if fetching the first autocomplete information takes about 8 seconds, and I keep typing with 2 characters per seconds, I end up with about 16 autocomplete requests. After the first 8 seconds, no further request is created, but the remaining requests take about 2 minutes to finish.

As a result, I have to wait for 2 minutes until I get a response to my actual query.

So I guess my initial proposal looks quite clever: Do not send any further autocomplete request if one is still pending. I still have to wait about 8 seconds until I get a response for my query. But if it takes about 6 seconds to actually type the query, waiting 2 more seconds is not an issue; 2 minutes in contrast actually is.

[Nisarg Jhaveri]

Yes, it is the case as far as I remember.

To fix this we have to add a condition in the function at https://github.com/phpmyadmin/phpmyadmin/blob/8dc1fe4727f73724a2fc3a7882574dd46a595e23/js/functions.js#L1734. I am not free for few more days. So, if not fixed, I'll try to fix it after some days.

[Marc Delisle]

Nisarg,
any progress?

[Nisarg Jhaveri]

Oops! forgot it. :/

On it.

[Nisarg Jhaveri]

See https://github.com/phpmyadmin/phpmyadmin/pull/1665

[Marc Delisle]

Fix from Nisarg merged, thanks.

Alexander, are you able to confirm by testing the QA_4_4 git branch ?

[Alexander Schuch]

I briefly played around some with QA_4_4. So far, the long waiting (multiple stacked requests) seems to be solved. Thanks.