MySQL: مقدمة إلى استعلامات MySQL وخطة التنفيذ EXPLAIN

-- The process of table lookup
SELECT * FROM users WHERE email = 'alice@email.com';
-- 1. Find id=1 in idx_email
-- 2. Find complete data for id=1 in PRIMARY

-- Create a composite index
CREATE INDEX idx_name_email ON users(username, email);

-- Covering Index（No need to return to the table）
SELECT username, email FROM users WHERE username = 'alice';

-- Non-covering index（Need to return to the table）
SELECT * FROM users WHERE username = 'alice';

WHERE a = 1                    -- ✅ Using index
WHERE a = 1 AND b = 2         -- ✅ Using index
WHERE a = 1 AND b = 2 AND c = 3  -- ✅ Using index
WHERE b = 2                    -- ❌ Index not used
WHERE b = 2 AND c = 3         -- ❌ Index not used

CREATE INDEX idx_abc ON orders(customer_id, status, order_date);

-- ✅ Using index
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid' AND order_date > '2026-01-01';

-- ❌ Index not used (Violates the Leftmost Prefix Rule)
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

-- ❌ Index Invalidation
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- ✅ Fix: Change to a range query
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- ❌ Index Invalidation
SELECT * FROM users WHERE phone = 13800001111;

-- ✅ Fix: Use quotes
SELECT * FROM users WHERE phone = '13800001111';

EXPLAIN SELECT * FROM users WHERE email = 'alice@email.com';

+----+-------+------+---------+------+----------+-------+
| id | type  | key  | key_len | ref  | rows     | Extra |
+----+-------+------+---------+------+----------+-------+
|  1 | const | idx_email | 402 | const | 1 |       |
+----+-------+------+---------+------+----------+-------+