Modern MySQL Features Every Developer Should Use

If your MySQL habits date from version 5.x, you're missing features that make queries simpler and schema changes safer.

Common table expressions

WITH monthly AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue
  FROM orders
  GROUP BY month
)
SELECT * FROM monthly WHERE revenue > 100000 ORDER BY month;

Window functions

SELECT customer_id, id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS nth_order,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;

CHECK constraints that are actually enforced

CREATE TABLE products (
  id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL,
  CONSTRAINT price_positive CHECK (price > 0),
  CONSTRAINT stock_not_negative CHECK (stock >= 0)
);

Before 8.0.16, MySQL parsed CHECK and silently ignored it.

Invisible indexes: test before dropping

ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE;  -- optimizer ignores it, still maintained
-- watch performance for a few days, then:
ALTER TABLE orders DROP INDEX idx_old_status;             -- or make it VISIBLE again

Descending and functional indexes

CREATE INDEX idx_orders_recent ON orders (customer_id, created_at DESC);
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

Instant schema changes

ALTER TABLE orders ADD COLUMN notes TEXT, ALGORITHM=INSTANT;  -- metadata only, no table copy

Other improvements

• EXPLAIN ANALYZE shows real execution times.
• SKIP LOCKED and NOWAIT for queue-style workloads.
• Better JSON functions, including JSON_TABLE.
• utf8mb4 as the default character set (full Unicode, including emoji).