FR: DB Queries revision and refactoring

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.

Awesome. Thank you for the knowledge transfer. We will copy your version into default config.