MySQL รองรับ Function-based Index แล้ว (Prefix Index / Generated Column)

MySQL ไม่รองรับ Function-based Index แบบ Oracle/PostgreSQL (Index บน Expression) โดยตรง แต่มีทางเลือก 2 แบบ

วิธีที่ 1: Prefix Index (Index บนส่วนต้นของ String) - รองรับทุก Version

CREATE TABLE FunctionIndexDemo (
    FirstName VARCHAR(100)
);

-- Index 6 ตัวอักษรแรก
CREATE INDEX indFirstName ON FunctionIndexDemo (FirstName(6));

ตรวจสอบ

SHOW INDEX FROM FunctionIndexDemo;
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table             | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| functionindexdemo | 1          | indFirstName | 1            | FirstName   | A         | 0           | 6        | NULL   | YES  | BTREE      |         |               | YES     |
+-------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
-- Sub_part = 6 หมายถึง Prefix Length 6

ใช้ได้เมื่อ

• Query: WHERE FirstName LIKE 'John%' (Prefix Search)
• Query: WHERE LEFT(FirstName, 6) = 'JohnSm'
• ❌ ไม่ใช้ได้: WHERE UPPER(FirstName) = 'JOHN', WHERE SUBSTRING(FirstName, 3, 4) = 'hnSm'

วิธีที่ 2: Generated Column + Index (MySQL 5.7+ / 8.0+) - แนะนำสำหรับ Function-based Index จริง

CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    -- Virtual Generated Column (ไม่ใช้พื้นที่ Disk)
    full_name_lower VARCHAR(201) GENERATED ALWAYS AS (LOWER(CONCAT(first_name, ' ', last_name))) VIRTUAL,
    -- Stored Generated Column (ใช้พื้นที่ Disk แต่ Query เร็วกว่า)
    email_domain VARCHAR(100) GENERATED ALWAYS AS (LOWER(SUBSTRING_INDEX(email, '@', -1))) STORED,
    
    INDEX idx_full_name_lower (full_name_lower),
    INDEX idx_email_domain (email_domain)
);

ใช้งาน

-- Query ใช้ Index ได้!
SELECT * FROM users WHERE full_name_lower = 'john doe';
SELECT * FROM users WHERE email_domain = 'gmail.com';

MySQL 8.0.13+: Functional Index (Native Support!)

-- MySQL 8.0.13+ รองรับ Index บน Expression โดยตรง
CREATE TABLE t (
    col VARCHAR(100),
    INDEX idx_lower_col ((LOWER(col)))
);

-- หรือ
ALTER TABLE t ADD INDEX idx_upper_col ((UPPER(col)));

✅ ใช้ได้กับ Functions: UPPER, LOWER, SUBSTRING, JSON_EXTRACT, CAST, DATE (Deterministic เท่านั้น)

เปรียบเทียบ

แนะนำ

• MySQL 8.0.13+: ใช้ Functional Index โดยตรง (Clean, No Schema Change)
• MySQL 5.7-8.0.12: ใช้ Generated Column (Stored) + Index
• Prefix Search: ใช้ Prefix Index (Simple, Space Efficient)
• หลีกเลี่ยง Non-deterministic Functions: NOW(), RAND(), UUID(), CONNECTION_ID()