คำสั่ง SQL สำหรับ DBA database administrator หรือ sysadmin ไว้หาตารางที่ ไม่มี index ในฐานข้อมูล MySQL mariaDB

[mindphp]

คำสั่ง SQL สำหรับ DBA database administrator หรือ sysadmin ไว้หาตารางที่ ไม่มี index ในฐานข้อมูล MySQL mariaDB

โค้ด: เลือกทั้งหมด

SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES AS t
LEFT JOIN information_schema.STATISTICS AS s ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'your_database_name'
AND s.INDEX_NAME IS NULL
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME;

แทนที่
your_database_name ด้วย ชื่อฐานข้อมูลของเรา

[mindphp]

แบบละเอียด

โค้ด: เลือกทั้งหมด

SELECT *
FROM INFORMATION_SCHEMA.TABLES AS MAIN_TABLE
WHERE
    TABLE_SCHEMA = 'your_database_name'
    -- ============================================================================
    -- FIND TABLES WITH A PRIMARY KEY
    -- ============================================================================
    AND TABLE_NAME IN (
                SELECT TABLE_NAME
                FROM (
                        SELECT TABLE_NAME, INDEX_NAME, COUNT(INDEX_NAME) AS TEST
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = 'your_database_name'
                AND INDEX_NAME = 'PRIMARY'
        GROUP BY TABLE_NAME , INDEX_NAME) AS TAB_IND_COLS
    GROUP BY TABLE_NAME)
    -- ============================================================================
    -- FIND TABLES WITH OUT ANY INDICES
    -- ============================================================================
AND TABLE_NAME NOT IN (
        SELECT TABLE_NAME
        FROM (
                SELECT TABLE_NAME, INDEX_NAME, COUNT(INDEX_NAME) AS TEST
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = 'your_database_name'
                AND INDEX_NAME <> 'PRIMARY'
        GROUP BY TABLE_NAME , INDEX_NAME) AS TAB_IND_COLS
    GROUP BY TABLE_NAME
    );

ตัวอย่างผลที่ได้

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY
def your_database_namephpbb_notification_emails BASE TABLE InnoDB 10 Dynamic 414530 49 20496384 0 0 4194304 NULL 2023-03-12 18:26:29 2023-09-23 18:56:27 NULL utf8_bin NULL 0 N
def your_database_namephpbb_m_announce_logs_edit_data BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 1 2023-03-12 18:26:04 NULL NULL utf8_bin NULL 0 N
def your_database_namephpbb_m_shoping_cart_member_data BASE TABLE InnoDB 10 Dynamic 4 4096 16384 0 0 0 5 2023-09-23 13:03:41 2023-09-23 18:49:29 NULL utf8_bin NULL 0 N
def your_database_name phpbb_extensions BASE TABLE MyISAM 10 Dynamic 66 20 1320 281474976710655 2048 0 67 2023-03-12 18:25:55 2023-03-12 18:25:55 NULL utf8_bin NULL 288230376151710720 N
def your_database_name phpbb_forums_access BASE TABLE MyISAM 10 Fixed 5 104 520 29273397577908223 2048 0 NULL 2023-03-12 18:25:55 2023-03-12 18:25:55 NULL utf8_bin NULL 1125899906841600 N
def your_database_name phpbb_disallow BASE TABLE MyISAM 10 Dynamic 17 21 364 281474976710655 2048 0 19 2023-03-12 18:25:54 2023-03-12 18:25:54 NULL utf8_bin NULL 288230376151710720 N
def your_database_name phpbb_m_shoping_cart_pricelist_data BASE TABLE InnoDB 10 Dynamic 41 399 16384 0