#315 Selecting table performs too many queries

[Anonymous]

Originally created by: ansgar.b...@wolterskluwer.de
Originally owned by: a...@anse.de

Reported as bug #1890886 by pdey@sf on 2008-02-10 19:17

Clicking on a table in the left hand-side tree view performs 8 queries,
when 2 would suffice.

Example; clicking on a table called 'MyTable':
SHOW /*!32332 FULL */ COLUMNS FROM `MyTable`
SHOW KEYS FROM `MyTable`
SELECT * FROM `MyTable` LIMIT 0, 50
SHOW TABLES LIKE 'Faq'
SHOW COLUMNS FROM `MyTable` LIKE '%'
SELECT COUNT(*) FROM `MyTable`
SHOW TABLES LIKE 'Faq'
SHOW COLUMNS FROM `MyTable` LIKE '%'

Apart from the redundant queries, the same functionality can be achieved
with:
DESCRIBE `MyTable`
SELECT * FROM `MyTable` LIMIT 0, 50

SELECT COUNT(*) on InnoDB is painfully slow.

Also, less queries is better...

**** Comment 1 by jemore@sf, 2008-02-11 07:54

Interesting, but the DESCRIBE action is not retreiving the comments of the
fields.
The "SHOW FULL COLUMNS" retreive the comments.

**** Comment 2 by pdey@sf, 2008-02-11 13:22

Sorry, should have clarified...
This is while on the 'Data' tab.

I realise now that the client probably updates the 'Table' tab too.

[Anonymous]

Originally posted by: rosenfie...@gmail.com

(No comment was entered for this change.)

Labels: -priority-medium Severity-Broken

[Anonymous]

Originally posted by: a...@anse.de

(No comment was entered for this change.)

Labels: -migrated

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Expanding a database node:

  SHOW TABLE STATUS FROM `def`

Displaying table definition:

  SHOW /*!32332 FULL */ COLUMNS FROM `cnpstaff`
  SHOW KEYS FROM `cnpstaff`

SHOW KEYS may be superfluous, because the SHOW COLUMNS statement returns limited key
info (in key / not in key) that may be enough to display the table definition view.

Show data:

  SELECT * FROM `abc` LIMIT 0, 50
  SHOW TABLES LIKE 'abc'
  SHOW COLUMNS FROM `abc` LIKE '%'
  SELECT COUNT(*) FROM `abc`
  SHOW TABLES LIKE 'abc'
  SHOW COLUMNS FROM `abc` LIKE '%'

'SELECT *' and 'SELECT COUNT(*)' are necessary.  Some InnoDB versions keep an
'approximate number of rows' counter which may be useful instead of the precise
count, which should be much faster.

Agree that these are superfluous (2x):

  SHOW TABLES LIKE 'abc'
  SHOW COLUMNS FROM `abc` LIKE '%'

Not sure what Zeos is trying to do with these.

[Anonymous]

Originally posted by: a...@anse.de

While the first one of these two looks very superflous, I think Zeos does its data
type detection via this second query.

[Anonymous]

Originally posted by: rosenfie...@gmail.com

That would be silly, since data types are included in the server response when you
do a query.

[Anonymous]

Originally posted by: a...@anse.de

Yes (silly), but I think that's the way Zeos does it to keep some kind of
compatibility with other supported DBMS or so. Could be hard to convince Zeos of not
doing that and instead rely on what a PMYSQL_RESULT returns. Probably not worth the
effort as this is more a minor performance issue.

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Not so sure.  If that was true,

  SELECT user AS max_connections FROM mysql.user

should fail or give 0, but it works as it should.

[Anonymous]

Originally posted by: rosenfie...@gmail.com

Fixed in [r1588] and [r1589].

Status: Fixed