83 Proven SQL Optimization Techniques to Supercharge Your Database

1. Avoid SELECT *

Scenario: Query user details while excluding sensitive fields.

-- ❌ SELECT * FROM users WHERE id = 1001; -- Returns password etc.
-- ✅ SELECT user_id, name, email, created_at FROM users WHERE id = 1001; -- Returns only needed columns

Typical use: Web API returning public user information.

2. Replace IN with EXISTS

Scenario: Find active customers' orders.

-- ❌ SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');
-- ✅ SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.status = 'active');

Typical use: E‑commerce platform filtering active orders.

3. Optimize Deep Pagination

Scenario: Paginate user behavior logs.

-- ❌ SELECT * FROM user_logs ORDER BY log_time DESC LIMIT 10 OFFSET 500000; -- Scans many rows
-- ✅ SELECT log.* FROM user_logs log JOIN (SELECT id FROM user_logs ORDER BY log_time DESC LIMIT 10 OFFSET 500000) tmp ON log.id = tmp.id; -- Uses index then fetches rows

Typical use: Backend admin view of historical logs.

4. Batch Update User Points

Scenario: Increase points for many users during an event.

-- ❌ UPDATE users SET points = points + 10 WHERE id = 1001; UPDATE users SET points = points + 10 WHERE id = 1002; ...
-- ✅ UPDATE users SET points = points + 10 WHERE id IN (1001, 1002, ..., 10000); -- Single statement updates 10k rows

Typical use: Bulk reward distribution.

5. UNION ALL to Replace OR Conditions

Scenario: Query logs with mixed conditions.

-- ❌ SELECT * FROM system_log WHERE log_type = 'error' OR source_module = 'api';
-- ✅ SELECT * FROM system_log WHERE log_type = 'error' UNION ALL SELECT * FROM system_log WHERE source_module = 'api' AND log_type != 'error';

Typical use: Monitoring system multi‑dimensional log search.

6. Avoid Function Conversion on Date Columns

Scenario: Count users born in 1990.

-- ❌ SELECT * FROM users WHERE YEAR(birthday) = 1990; -- Index not used
-- ✅ SELECT * FROM users WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31'; -- Uses date index

Typical use: Age‑group user profiling.

7. Small Table Drives Large Table Join Order

Scenario: Join department (small) with sales (large).

-- ❌ SELECT s.* FROM sales s JOIN departments d ON s.dept_id = d.id;
-- ✅ SELECT /*+ STRAIGHT_JOIN */ s.* FROM departments d JOIN sales s ON d.id = s.dept_id; -- Forces small table first

Typical use: Data warehouse dimension‑fact join.

8. Covering Index for Order Queries

Scenario: Retrieve order summary.

CREATE INDEX idx_user_orders ON orders (user_id, order_date, amount);
SELECT user_id, order_date, amount FROM orders WHERE user_id = 2003; -- Index‑only scan

Typical use: Fast order list page.

9. Early Constant Filtering

Scenario: Filter sales by region and category before join.

-- ❌ SELECT * FROM sales s JOIN products p ON s.product_id = p.id WHERE s.region = 'Asia' AND p.category = 'Electronics';
-- ✅ SELECT * FROM sales s JOIN products p ON s.product_id = p.id WHERE s.region = 'Asia' AND p.category = 'Electronics'; -- Filter region first reduces rows

Typical use: BI report with multi‑level filters.

10. Avoid Implicit Type Conversion

Scenario: Query devices by string ID.

-- ❌ SELECT * FROM devices WHERE device_id = 12345; -- device_id is VARCHAR
-- ✅ SELECT * FROM devices WHERE device_id = '12345';

Typical use: IoT device precise lookup.

11. CASE WHEN Instead of Multiple Scans

Scenario: Count orders by status in one scan.

SELECT COUNT(*) AS total_orders,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
       SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped_orders
FROM orders;

Typical use: Order status dashboard.

12. Partition Table for Time‑Range Queries

Scenario: Query logs from the last 30 days.

CREATE TABLE server_logs (
  id INT,
  log_content TEXT,
  log_time DATETIME
) PARTITION BY RANGE (TO_DAYS(log_time)) (
  PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-10-01')),
  PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01'))
);
SELECT * FROM server_logs WHERE log_time BETWEEN NOW() - INTERVAL 30 DAY AND NOW();

Typical use: Time‑series log analysis.

13. Function Index for JSON Queries

Scenario: Filter products by JSON attribute.

-- ❌ Full scan
SELECT * FROM products WHERE JSON_EXTRACT(specs, '$.weight') > 10;
-- ✅ Create function index
CREATE INDEX idx_product_weight ON products ((JSON_EXTRACT(specs, '$.weight')));
SELECT * FROM products WHERE JSON_EXTRACT(specs, '$.weight') > 10; -- Uses index

Typical use: E‑commerce product spec search.

14. Replace Cursors with Batch CASE WHEN

Scenario: Update user discounts based on level.

UPDATE users SET discount = CASE
  WHEN level = 'VIP' THEN 0.8
  WHEN level = 'Premium' THEN 0.9
  ELSE 1.0
END;

Typical use: Membership benefit bulk update.

15. Materialized View for Fast Aggregation

Scenario: Daily sales leaderboard.

CREATE MATERIALIZED VIEW daily_top_sales REFRESH EVERY 5 MINUTE AS
SELECT product_id, SUM(amount) AS total_sales FROM orders WHERE order_date = CURDATE() GROUP BY product_id;
SELECT * FROM daily_top_sales ORDER BY total_sales DESC LIMIT 10;

Typical use: Real‑time dashboard.

16. Compress Archive Tables

Scenario: Archive historic orders.

CREATE TABLE orders_archive LIKE orders INCLUDING INDEXES COMPRESSION='ZLIB';
INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < '2020-01-01';
DELETE FROM orders WHERE order_date < '2020-01-01';

Typical use: Financial system historical data storage.

17. Flatten Nested Views

Scenario: Avoid performance hit from view nesting.

-- ❌ Nested view
CREATE VIEW v_orders AS SELECT * FROM orders WHERE status = 'completed';
CREATE VIEW v_user_orders AS SELECT u.name, v.* FROM users u JOIN v_orders v ON u.id = v.user_id;
-- ✅ Direct join
SELECT u.name, o.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed';

Typical use: Reporting system.

18. Bitmap Index for Low‑Cardinality Columns

Scenario: Filter by gender.

CREATE BITMAP INDEX idx_bm_gender ON users(gender);
SELECT * FROM users WHERE gender = 'F';

Typical use: Data warehouse dimension filter.

19. Avoid Triggers for High‑Concurrency Counters

Scenario: Increment order count per user.

-- ❌ Row‑by‑row trigger
CREATE TRIGGER update_order_count AFTER INSERT ON orders FOR EACH ROW UPDATE user_stats SET order_count = order_count + 1 WHERE user_id = NEW.user_id;
-- ✅ Asynchronous batch update
INSERT INTO order_created_events (user_id) VALUES (123);
-- Periodic job aggregates events and updates user_stats

Typical use: High‑concurrency counter.

20. Columnar Storage for Large Analytic Queries

Scenario: Billion‑row aggregation.

CREATE TABLE sales_columnar (
  sale_id INT,
  product_type VARCHAR(20),
  price DECIMAL(10,2),
  quantity INT
) ENGINE=ColumnStore;
SELECT product_type, AVG(price), MAX(quantity) FROM sales_columnar GROUP BY product_type;

Typical use: OLAP data warehouse.

21. Temporary Table to Break Complex Queries

Scenario: Multi‑step report generation.

CREATE TEMPORARY TABLE temp_dept_stats AS
SELECT d.id AS dept_id, d.name AS dept_name, COUNT(e.id) AS emp_count
FROM departments d LEFT JOIN employees e ON d.id = e.dept_id GROUP BY d.id, d.name;
CREATE TEMPORARY TABLE temp_salary_stats AS
SELECT e.dept_id, AVG(s.salary) AS avg_salary FROM employees e JOIN salaries s ON e.id = s.emp_id GROUP BY e.dept_id;
SELECT t1.dept_name, t1.emp_count, t2.avg_salary FROM temp_dept_stats t1 JOIN temp_salary_stats t2 ON t1.dept_id = t2.dept_id;

Typical use: Data‑warehouse ETL staging.

22. Window Functions Instead of Self‑Join

Scenario: Rank employees by salary within each department.

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Typical use: Performance ranking.

23. Index Condition Pushdown (ICP)

Scenario: Composite index with additional non‑indexed filter.

SELECT * FROM users WHERE age > 20 AND city = 'Beijing' AND name LIKE '张%'; -- Modern MySQL pushes name filter to storage engine automatically

Typical use: E‑commerce multi‑condition product search.

24. Asynchronous Bulk Delete

Scenario: Delete tens of millions of old logs.

DELIMITER $$
CREATE PROCEDURE batch_delete()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  WHILE NOT done DO
    DELETE FROM user_logs WHERE created_at < '2020-01-01' LIMIT 10000;
    SET done = ROW_COUNT() = 0;
    COMMIT;
    DO SLEEP(1);
  END WHILE;
END$$
DELIMITER ;
CALL batch_delete();

Typical use: Log system periodic cleanup.

25. Full‑Text Index Instead of LIKE

Scenario: Search product titles.

ALTER TABLE products ADD FULLTEXT INDEX idx_title (title);
SELECT * FROM products WHERE MATCH(title) AGAINST('智能手机' IN NATURAL LANGUAGE MODE);

Typical use: Content retrieval or e‑commerce search.

26. Generated Column for JSON Extraction

Scenario: Frequently query JSON field.

ALTER TABLE users ADD COLUMN phone VARCHAR(20) GENERATED ALWAYS AS (JSON_EXTRACT(profile, '$.contact.phone')) STORED;
CREATE INDEX idx_phone ON users(phone);
SELECT id, phone FROM users;

Typical use: Frequent JSON attribute access.

27. Recursive CTE for Hierarchical Data

Scenario: Retrieve full department tree.

WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id, 1 AS level FROM departments WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, dt.level + 1 FROM departments d JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree ORDER BY level, id;

Typical use: Organizational chart.

28. Avoid HAVING Abuse

Scenario: Filter groups after aggregation.

SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 5;
-- Better
SELECT user_id, COUNT(*) AS order_count FROM orders WHERE EXISTS (SELECT 1 FROM order_details od WHERE od.order_id = orders.id AND od.status = 'valid') GROUP BY user_id HAVING COUNT(*) > 5;

Typical use: User behavior analysis.

29. R‑Tree Spatial Index

Scenario: Find nearby gas stations.

ALTER TABLE gas_stations ADD SPATIAL INDEX(location);
SELECT * FROM gas_stations WHERE MBRContains(ST_Buffer(POINT(116.4074,39.9042), 5000), location);

Typical use: LBS or geofence queries.

30. INSERT … ON DUPLICATE KEY UPDATE

Scenario: Record user last login.

INSERT INTO user_login (user_id, last_login, login_count) VALUES (123, NOW(), 1)
ON DUPLICATE KEY UPDATE last_login = NOW(), login_count = login_count + 1;

Typical use: Counter or status update in high‑concurrency.

31. Group Commit for High‑Throughput Writes

Scenario: IoT devices batch data.

INSERT INTO device_data (device_id, value) VALUES (1001, 23.5), (1002, 18.7), ... ;
SET GLOBAL innodb_flush_log_at_trx_commit = 2; -- Trade‑off durability for speed

Typical use: IoT or log ingestion.

32. SKIP LOCKED for Queue Workers

Scenario: Distributed task scheduling.

BEGIN;
SELECT * FROM tasks WHERE status = 'pending' ORDER BY priority DESC FOR UPDATE SKIP LOCKED LIMIT 1;
UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;

Typical use: High‑concurrency task queue.

33. Partial Index (Conditional Index)

Scenario: Query only active orders.

CREATE INDEX idx_active_user_orders ON orders(user_id) WHERE status = 'active';
SELECT * FROM orders WHERE user_id = 100 AND status = 'active';

Typical use: E‑commerce active order lookup.

34. Hash Index for In‑Memory Tables

Scenario: Fast primary‑key lookup.

CREATE TABLE session_data (session_id CHAR(32) PRIMARY KEY, data BLOB) ENGINE=MEMORY; -- Uses hash index

Typical use: Session storage.

35. Generated Column for Date Range Queries

Scenario: Optimize string‑date range filter.

ALTER TABLE logs ADD COLUMN log_date_real DATE GENERATED ALWAYS AS (STR_TO_DATE(log_date, '%Y-%m-%d')) STORED;
CREATE INDEX idx_log_date ON logs(log_date_real);
SELECT * FROM logs WHERE log_date_real BETWEEN '2023-01-01' AND '2023-01-31';

Typical use: Legacy system date field optimization.

36. Pivot (Conditional Aggregation) for Wide Tables

Scenario: Transform attribute rows to columns.

SELECT user_id,
       MAX(CASE WHEN type = 'email' THEN value END) AS email,
       MAX(CASE WHEN type = 'phone' THEN value END) AS phone
FROM attributes GROUP BY user_id;

Typical use: User attribute wide‑table generation.

37. LATERAL JOIN to Replace Correlated Subqueries

Scenario: Get each user's latest order.

SELECT u.id, u.name, o.order_id
FROM users u LEFT JOIN LATERAL (
  SELECT order_id FROM orders WHERE user_id = u.id ORDER BY order_date DESC LIMIT 1
) o ON TRUE;

Typical use: Per‑group top‑N retrieval.

38. Bloom Filter Index for Large Joins

Scenario: Join billions‑row user table with massive behavior table.

-- Hive example
SET hive.bloom.filter.enabled=true;
CREATE INDEX idx_user_id ON TABLE behavior (user_id) AS 'BLOOMFILTER' WITH DEFERRED REBUILD;
ALTER INDEX idx_user_id ON behavior REBUILD;

Typical use: Big‑data ecosystem join optimization.

39. Vectorized Execution for Massive Aggregations

Scenario: Aggregate billions of sales rows.

SET max_worker_processes = 8;
SET enable_vectorized_engine = on;
SELECT product_id, AVG(price), MAX(quantity) FROM sales GROUP BY product_id;

Typical use: Data‑warehouse analytical queries.

40. Expression Index for Concatenated Names

Scenario: Search by full name.

CREATE INDEX idx_full_name ON employees ((CONCAT(first_name, ' ', last_name)));
SELECT * FROM employees WHERE CONCAT(first_name, ' ', last_name) = '张三';

Typical use: Frequent full‑name lookups.

41. BRIN Index for Time‑Series Data

Scenario: Query recent sensor data.

CREATE INDEX idx_brin_time ON sensor_data USING BRIN(record_time);
SELECT * FROM sensor_data WHERE record_time BETWEEN '2023-01-01' AND '2023-01-02';

Typical use: IoT time‑series queries.

42. MVCC to Reduce Lock Contention

Scenario: High‑concurrency account balance update.

UPDATE accounts SET balance = balance - 100 WHERE id = 1001 AND balance >= 100;

Typical use: Financial account updates.

43. Column Compression to Reduce I/O

Scenario: Store large text articles.

CREATE TABLE articles (id INT PRIMARY KEY, content TEXT COMPRESSED);
SELECT content FROM articles WHERE id = 1001; -- 70% less I/O

Typical use: CMS or log storage.

44. Foreign‑Key Index for Cascading Deletes

Scenario: Delete user and automatically remove orders.

CREATE INDEX idx_orders_user_id ON orders(user_id);
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Typical use: Tight relational cascade.

45. Temporary Table Cache for Repeated Subqueries

Scenario: Complex report reuses same aggregation.

CREATE TEMPORARY TABLE temp_user_orders AS
SELECT user_id, AVG(amount) AS avg_order, MAX(amount) AS max_order FROM orders GROUP BY user_id;
SELECT u.*, t.avg_order, t.max_order FROM users u LEFT JOIN temp_user_orders t ON u.id = t.user_id;

Typical use: Data‑warehouse reporting.

46. Asynchronous Commit for Log Writes

Scenario: High‑throughput log insertion.

SET synchronous_commit = off; -- PostgreSQL async commit
INSERT INTO access_log (...) VALUES (...), (...), ...;

Typical use: Log collection.

47. Connection Pooling to Reduce Overhead

Scenario: Web app high‑concurrency DB access.

$conn = $connection_pool->get_connection();
$result = $conn->query('SELECT ...');
$connection_pool->release_connection($conn);

Typical use: Any backend service.

48. Prepared Statements for Security and Performance

Scenario: Dynamic condition query.

PreparedStatement ps = conn.prepareStatement('SELECT * FROM users WHERE name = ?');
ps.setString(1, name);
ResultSet rs = ps.executeQuery();

Typical use: All dynamic SQL.

49. Database Proxy for Read‑Write Splitting

Scenario: Read‑heavy application.

$conn = connect_to_proxy();
$conn->query('UPDATE ...'); // routed to master
$conn->query('SELECT ...'); // routed to replica

Typical use: E‑commerce, social apps.

50. SQL Hints to Force Index

Scenario: Optimizer chooses full scan.

SELECT * FROM orders FORCE INDEX (idx_status_amount) WHERE status = 'shipped' AND amount > 1000;

Typical use: Emergency performance fix.

51. Incremental Refresh Materialized View

Scenario: Real‑time sales aggregation.

CREATE MATERIALIZED VIEW LOG ON sales;
CREATE MATERIALIZED VIEW daily_sales REFRESH FAST ON COMMIT AS
SELECT sale_date, SUM(amount) AS total_sales FROM sales GROUP BY sale_date;

Typical use: Real‑time dashboard.

52. Function Index for JSONB Path Queries

Scenario: Fast JSONB attribute filter.

CREATE INDEX idx_product_width ON products ((CAST(JSON_EXTRACT(specs, '$.dimensions.width') AS INTEGER)));
SELECT * FROM products WHERE CAST(JSON_EXTRACT(specs, '$.dimensions.width') AS INTEGER) > 100;

Typical use: Product spec search.

53. Partition Exchange for Zero‑Downtime Archiving

Scenario: Archive old orders without locking.

CREATE TABLE orders_archive LIKE orders PARTITION BY RANGE (order_date);
ALTER TABLE orders EXCHANGE PARTITION p2021 WITH TABLE orders_archive WITHOUT VALIDATION;

Typical use: Financial historical data migration.

54. Parallel Index Scan for Large Table

Scenario: Billion‑row user range query.

SET max_parallel_workers_per_gather = 8;
CREATE INDEX idx_users_created ON users(created_at);
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';

Typical use: Big‑data platform time‑range queries.

55. Prepared Statement Caching in Connection Pool

Scenario: High‑frequency short queries.

const stmt = pool.prepare('SELECT * FROM products WHERE id = ?');
stmt.execute([product_id]);

Typical use: Microservice query interfaces.

56. GIN Index for Array Containment

Scenario: Find articles with specific tag.

CREATE INDEX idx_article_tags ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['technology'];

Typical use: CMS tag filtering.

57. Hash Aggregation Instead of Sort

Scenario: Large group‑by statistics.

SET enable_sort = off; -- Forces hash aggregation

Typical use: Data‑warehouse large group queries.

58. TTL for Automatic Expiration

Scenario: Auto‑clean verification codes.

CREATE TABLE sms_codes (
  id SERIAL PRIMARY KEY,
  phone VARCHAR(20),
  code VARCHAR(6),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) WITH (ttl_expiration_expression = 'created_at + INTERVAL ''10 minutes''');

Typical use: Temporary data like sessions or captchas.

59. Columnar Index for Fast Aggregation

Scenario: Real‑time analysis of billions of rows.

CREATE TABLE sales (
  product_id UInt32,
  quantity Float32
) ENGINE = AggregatingMergeTree() ORDER BY product_id;

Typical use: Real‑time analytics dashboard.

60. Asynchronous Materialized View Across Data Centers

Scenario: Combine data from two databases.

CREATE MATERIALIZED VIEW global_view REFRESH EVERY 5 MINUTE AS
SELECT * FROM db1.a UNION ALL SELECT * FROM db2.b;

Typical use: Distributed system data aggregation.

61. Bitmap Index for Multi‑Value Tag Queries

Scenario: Query users by multiple tags.

CREATE BITMAP INDEX idx_tags ON users(tags);
SELECT * FROM users WHERE tags IN ('tag1','tag2','tag3');

Typical use: User‑profile tag search.

62. Vector Index for Similarity Search

Scenario: Image feature vector lookup.

CREATE INDEX idx_image_vector ON images USING ivfflat (feature_vector vector_cosine_ops);
SELECT * FROM images ORDER BY feature_vector <-> '[0.12,0.34,...]' LIMIT 10;

Typical use: AI content retrieval.

63. Keyset Pagination Instead of OFFSET

Scenario: Infinite scroll.

SELECT * FROM posts WHERE created_at < '2023-06-01' ORDER BY created_at DESC LIMIT 10;

Typical use: Social media feed.

64. Global Secondary Index for Sharded Tables

Scenario: Query non‑shard key across shards.

CREATE GLOBAL INDEX idx_user_orders ON orders(user_id);
SELECT * FROM orders WHERE user_id = 123;

Typical use: Distributed database query.

65. Column Compression for Large Text

Scenario: Store massive article bodies.

CREATE TABLE articles (id INT PRIMARY KEY, content TEXT COMPRESSED WITH (compression_level = 7));

Typical use: CMS or log storage.

66. In‑Memory Table for Temporary Computation

Scenario: Intermediate results in complex report. CREATE TABLE temp_results (...) ENGINE=MEMORY; Typical use: Complex report step.

67. Expression Index for Age Range

Scenario: Query users aged 20‑30.

CREATE INDEX idx_user_age ON users ((EXTRACT(YEAR FROM age(birth_date))));
SELECT * FROM users WHERE EXTRACT(YEAR FROM age(birth_date)) BETWEEN 20 AND 30;

Typical use: Age‑segment statistics.

68. Partition Pruning for Time‑Series Logs

Scenario: Query logs for a specific day.

CREATE TABLE logs (... ) PARTITION BY RANGE (log_time);
SELECT * FROM logs WHERE log_time BETWEEN '2023-06-01' AND '2023-06-02'; -- Only relevant partitions scanned

Typical use: IoT device data.

69. Connection Reuse via Pool

Scenario: Microservice high‑frequency DB access.

const conn = pool.acquire();
conn.query('SELECT ...');
pool.release(conn);

Typical use: Cloud‑native services.

70. Database Proxy for Automatic Sharding Routing

Scenario: Application queries without manual shard logic.

SELECT * FROM users WHERE user_id = 123; -- Proxy routes to correct shard

Typical use: Massive scale sharded systems.

71. Hot‑Cold Data Tiering

Scenario: Store historic orders on cheaper storage.

ALTER TABLE orders SET (storage_policy = 'HOT:30d COLD:1y ARCHIVE:5y');

Typical use: E‑commerce order history.

72. Saga Pattern for Distributed Transactions

Scenario: Create order across services.

1) INSERT INTO orders (status='pending') ...
2) UPDATE inventory SET lock_qty = lock_qty + 1 ...
3) UPDATE orders SET status='confirmed' ...
-- Compensation steps on failure

Typical use: Microservice order workflow.

73. Multi‑Tenant Partial Indexes

Scenario: SaaS tenant‑specific queries.

CREATE INDEX idx_tenant1_data ON all_data (data) WHERE tenant_id = 1;

Typical use: Isolated tenant performance.

74. HTAP Mixed‑Workload Isolation

Scenario: OLTP and OLAP on same data.

-- OLTP uses row store, OLAP uses columnar replica
SET replica_query = 'columnar';

Typical use: Real‑time analytics with transactional workload.

75. Resource Groups for Critical Business

Scenario: Reserve CPU for core transactions.

CREATE RESOURCE GROUP critical CPU_RATE_LIMIT = 70;
CREATE RESOURCE GROUP report CPU_RATE_LIMIT = 30;
ALTER USER trade_user SET RESOURCE GROUP critical;
ALTER USER report_user SET RESOURCE GROUP report;

Typical use: Financial transaction systems.

76. Federated Query Engine

Scenario: Join local and remote tables.

CREATE SERVER remote_db FOREIGN DATA WRAPPER mysql;
CREATE FOREIGN TABLE remote_products (...) SERVER remote_db;
SELECT local.*, remote.stock FROM local_products local JOIN remote_products remote ON local.id = remote.product_id;

Typical use: Data‑platform cross‑source queries.

77. AI‑Driven Adaptive Indexes

Scenario: Auto‑adjust indexes for hot products.

ALTER TABLE products ADD INDEX auto_idx USING 'adaptive' WITH (refresh_interval='1h');

Typical use: Traffic‑spiky e‑commerce.

78. Vectorized UDF for Fast Computation

Scenario: Real‑time risk scoring.

CREATE FUNCTION vectorized_risk_score(VECTOR) RETURNS FLOAT AS ... LANGUAGE PL/python;
SELECT id, vectorized_risk_score(data_vector) FROM loans;

Typical use: Credit risk engine.

79. Zero‑Copy Data Import

Scenario: Daily bulk load.

ALTER TABLE target ATTACH PARTITION source FROM 's3://bucket/data.parquet';

Typical use: Data‑warehouse ETL.

80. Blockchain‑Style Immutable Tables

Scenario: Auditable historical records.

CREATE TABLE orders (id INT PRIMARY KEY, ...) WITH (SYSTEM_VERSIONING = ON);
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2023-01-01';

Typical use: Financial audit trails.

81. Dynamic Data Masking with Performance

Scenario: Protect PII in analytics queries.

CREATE VIEW vw_users_analytics AS
SELECT user_id,
       name,
       mask(phone_number, 'partial(3,"xxxx",4)') AS phone_number,
       mask(email, 'email()') AS email
FROM users;
SELECT * FROM vw_users_analytics WHERE region = 'North';

Typical use: Secure analytics.

82. Cost‑Based Optimizer Hints

Scenario: Force join order for complex query.

SELECT /*+ LEADING(c b a) USE_NL(b a) */ *
FROM large_table_a a
JOIN large_table_b b ON a.key = b.key
JOIN small_table_c c ON b.other_key = c.other_key;

Typical use: Emergency performance tuning.

83. Immutable Append‑Only Tables

Scenario: Log or event stream that never updates.

CREATE TABLE event_log (
  id BIGSERIAL PRIMARY KEY,
  event_time TIMESTAMP,
  user_id INT,
  event_data JSONB
) WITH (appendonly = true, orientation = column, compresstype = zstd);

Typical use: Time‑series logs, event sourcing.

These 83 SQL optimization scenarios demonstrate that performance tuning goes far beyond simply adding indexes; it involves thoughtful query rewriting, leveraging advanced storage features, using modern execution engines, and, when necessary, adjusting architecture to match workload characteristics.