perf: Use Optimized Queries for MySQLQueryRunner.loadTables

[imnotjames]

This changes how we make queries against the MySQL INFORMATION_SCHEMA metadata tables to follow the performance guidelines in the MySQL documentation. This means that the queries are a LITTLE weird, but I included pretty verbose comments on the implementation.

I did an EXPLAIN of every query that this generates & runs - each one requires the minimum amount of data to operate on & doesn't scan the database directories.

This is verified via the tests in other locations that it's working still - Not sure the best technique to verify this.

closes #6820

fixes #6800

[imnotjames]

Queries executed are as follows:

SELECT
    `TABLE_SCHEMA`,
    `TABLE_NAME`
FROM
    `INFORMATION_SCHEMA`.`TABLES`
WHERE
        `TABLE_SCHEMA` = 'test'
  AND
        `TABLE_NAME` = 'question'
UNION
SELECT
    `TABLE_SCHEMA`,
    `TABLE_NAME`
FROM
    `INFORMATION_SCHEMA`.`TABLES`
WHERE
        `TABLE_SCHEMA` = 'test'
  AND
        `TABLE_NAME` = 'category';

SELECT
    *
FROM
    `INFORMATION_SCHEMA`.`COLUMNS`
WHERE
        `TABLE_SCHEMA` = 'test'
  AND
        `TABLE_NAME` = 'question'
UNION
SELECT
    *
FROM
    `INFORMATION_SCHEMA`.`COLUMNS`
WHERE
        `TABLE_SCHEMA` = 'test'
  AND
        `TABLE_NAME` = 'category';

SELECT * FROM (
                  SELECT
                      *
                  FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
                  WHERE
                          `kcu`.`TABLE_SCHEMA` = 'test'
                    AND
                          `kcu`.`TABLE_NAME` = 'question'
                  UNION
                  SELECT
                      *
                  FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
                  WHERE
                          `kcu`.`TABLE_SCHEMA` = 'test'
                    AND
                          `kcu`.`TABLE_NAME` = 'category'
              ) `kcu` WHERE `CONSTRAINT_NAME` = 'PRIMARY';

SELECT
    `SCHEMA_NAME`, `DEFAULT_CHARACTER_SET_NAME` as `CHARSET`, `DEFAULT_COLLATION_NAME` AS `COLLATION` FROM `INFORMATION_SCHEMA`.`SCHEMATA`;

SELECT
    `s`.*
FROM (
         SELECT
             *
         FROM `INFORMATION_SCHEMA`.`STATISTICS`
         WHERE
                 `TABLE_SCHEMA` = 'test'
           AND
                 `TABLE_NAME` = 'question'
         UNION
         SELECT
             *
         FROM `INFORMATION_SCHEMA`.`STATISTICS`
         WHERE
                 `TABLE_SCHEMA` = 'test'
           AND
                 `TABLE_NAME` = 'category'
     ) `s`
         LEFT JOIN (
    SELECT
        *
    FROM `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
    WHERE
            `CONSTRAINT_SCHEMA` = 'test'
      AND
            `TABLE_NAME` = 'question'
    UNION
    SELECT
        *
    FROM `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
    WHERE
            `CONSTRAINT_SCHEMA` = 'test'
      AND
            `TABLE_NAME` = 'category'
) `rc`
                   ON
                               `s`.`INDEX_NAME` = `rc`.`CONSTRAINT_NAME`
                           AND
                               `s`.`TABLE_SCHEMA` = `rc`.`CONSTRAINT_SCHEMA`
WHERE
        `s`.`INDEX_NAME` != 'PRIMARY'
  AND
    `rc`.`CONSTRAINT_NAME` IS NULL;

SELECT
    `kcu`.`TABLE_SCHEMA`,
    `kcu`.`TABLE_NAME`,
    `kcu`.`CONSTRAINT_NAME`,
    `kcu`.`COLUMN_NAME`,
    `kcu`.`REFERENCED_TABLE_SCHEMA`,
    `kcu`.`REFERENCED_TABLE_NAME`,
    `kcu`.`REFERENCED_COLUMN_NAME`,
    `rc`.`DELETE_RULE` `ON_DELETE`,
    `rc`.`UPDATE_RULE` `ON_UPDATE`
FROM (
         SELECT
             *
         FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
         WHERE
                 `kcu`.`TABLE_SCHEMA` = 'test'
           AND
                 `kcu`.`TABLE_NAME` = 'question'
         UNION
         SELECT
             *
         FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` `kcu`
         WHERE
                 `kcu`.`TABLE_SCHEMA` = 'test'
           AND
                 `kcu`.`TABLE_NAME` = 'category'
     ) `kcu`
         INNER JOIN (
    SELECT
        *
    FROM `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
    WHERE
            `CONSTRAINT_SCHEMA` = 'test'
      AND
            `TABLE_NAME` = 'question'
    UNION
    SELECT
        *
    FROM `INFORMATION_SCHEMA`.`REFERENTIAL_CONSTRAINTS`
    WHERE
            `CONSTRAINT_SCHEMA` = 'test'
      AND
            `TABLE_NAME` = 'category'
) `rc`
                    ON
                                `rc`.`CONSTRAINT_SCHEMA` = `kcu`.`CONSTRAINT_SCHEMA`
                            AND
                                `rc`.`TABLE_NAME` = `kcu`.`TABLE_NAME`
                            AND
                                `rc`.`CONSTRAINT_NAME` = `kcu`.`CONSTRAINT_NAME`;

@ronits-cx What do you think?

[ronits-cx]

@imnotjames 👍
Looks thorough, even though like you said the queries constructed are long but it's easy to understand their different parts from the code
Thanks for giving this attention :)