SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
FR: DB Queries revision and refactoring

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
FR: DB Queries revision and refactoring
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post FR: DB Queries revision and refactoring Reply with quote
In older versions of MariaDB (5.x) the sources of system views in information schema are not "well" indexed. This results in some DB Queries that come as factory defaults, like the one named Columns (MariaDB) + Keys a
Code:

SELECT
   COLUMN_NAME, COLUMN_TYPE,
   CONCAT(
      CASE WHEN EXTRA = 'auto_increment' THEN 'A'
      ELSE SUBSTR(IS_NULLABLE,1,1)/*N,Y*/
      END,
      CASE /*P,F,U*/
       WHEN COLUMN_KEY = 'PRI' THEN 'P'
      WHEN EXISTS (
         SELECT NULL FROM information_schema.KEY_COLUMN_USAGE k
            WHERE k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME AND k.COLUMN_NAME = c.COLUMN_NAME
            AND k.REFERENCED_COLUMN_NAME IS NOT NULL
       ) THEN 'F'
      WHEN COLUMN_KEY = 'UNI' THEN 'U'
      ELSE ' '
      END,
      IF(COLUMN_KEY = 'MUL','I','')
    )
FROM
   information_schema.COLUMNS c
WHERE
   TABLE_SCHEMA = :SCHEMA_NAME
AND TABLE_NAME = :OBJECT_NAME
ORDER BY
   ORDINAL_POSITION

to achieve some fantastic (and nerve-wracking) long run times, for example anywhere between 2 to 7 seconds.

With some careful refactoring, this can be shaved down to ~40 ms, using this code instead:
Code:

SELECT
   COLUMN_NAME, COLUMN_TYPE,
   CONCAT(
      CASE WHEN EXTRA = 'auto_increment' THEN 'A'
      ELSE SUBSTR(IS_NULLABLE,1,1)/*N,Y*/
      END,
      CASE /*P,F,U*/
       WHEN COLUMN_KEY = 'PRI' THEN 'P'
      WHEN EXISTS (
         SELECT NULL FROM information_schema.KEY_COLUMN_USAGE k
            WHERE k.TABLE_SCHEMA = :SCHEMA_NAME AND k.TABLE_NAME = :OBJECT_NAME AND k.COLUMN_NAME = c.COLUMN_NAME
            AND k.REFERENCED_COLUMN_NAME IS NOT NULL
       ) THEN 'F'
      WHEN COLUMN_KEY = 'UNI' THEN 'U'
      ELSE ' '
      END,
      IF(COLUMN_KEY = 'MUL','I','')
    )
FROM
   information_schema.COLUMNS c
WHERE
   TABLE_SCHEMA = :SCHEMA_NAME
AND TABLE_NAME = :OBJECT_NAME
ORDER BY
   ORDINAL_POSITION


This short-circuiting definitely cannot be done in each of the DB queries and in most cases, the performance gain will likely not justify doing it even if it were possible, but in some cases (like the mentioned old version of MariaDB) it really makes the difference.
Sat Dec 03, 2022 8:34 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Awesome. Thank you for the knowledge transfer. We will copy your version into default config.
Sat Dec 03, 2022 9:58 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.