ইনডেক্সিং (Indexing) — পারফরম্যান্স অপটিমাইজেশনের চাবিকাঠি
আপনার ডেটাবেসে যদি ১০টি ডেটা থাকে, তবে ইনডেক্স ছাড়াও চলবে। কিন্তু যখন ডেটা ১০ লক্ষে (1 Million) পৌঁছাবে, তখন ইনডেক্স ছাড়া একটি কুয়েরি রান করতে ৩-৪ সেকেন্ড লেগে যেতে পারে। আর ইনডেক্স থাকলে সেটা লাগবে ০.০০৫ সেকেন্ড।
এই অধ্যায়ে আমরা শিখব কীভাবে ডেটাবেসকে সুপারফাস্ট রকেটে পরিণত করা যায়।
৮.১ Index কী? (সহজ এবং গভীর ধারণা)
👉 সহজ কথায় (Beginner):
বইয়ের পেছনে যেমন সূচিপত্র বা Index থাকে, যা দেখে আপনি দ্রুত নির্দিষ্ট টপিক খুঁজে পান, ডেটাবেসেও ইনডেক্স ঠিক তাই। ইনডেক্স ছাড়া MySQL-কে বইয়ের প্রথম পাতা থেকে শেষ পাতা পর্যন্ত (Full Table Scan) খুঁজতে হয়।
👉 ইঞ্জিনিয়ারিং ভাষায় (Senior):
ইনডেক্স হলো একটি বিশেষ Data Structure (মূলত B+ Tree), যা ডিস্কের ওপর ডেটাকে একটি সাজানো (Sorted) অর্ডারে পয়েন্ট করে রাখে।
- Without Index: Time Complexity O(n) — লিনিয়ার সার্চ (খুব স্লো)।
- With Index: Time Complexity O(log n) — বাইনারি সার্চ স্টাইল (সুপার ফাস্ট)।
৮.২ MySQL Index Types (কোনটা কখন?)
MySQL-এ বিভিন্ন কাজের জন্য বিভিন্ন ইনডেক্স আছে।
🌲 B-Tree Index (The Default King)
আমরা সাধারণত CREATE INDEX দিলে এটাই তৈরি হয়।
এটি রেঞ্জ কুয়েরি (<, >, BETWEEN), ORDER BY এবং Exact Match সব কিছুতেই কাজ করে।
🔗 Hash Index
এটি Key-Value পেয়ারের মতো কাজ করে।
শুধুমাত্র = (সমান) অপারেশনে কাজ করে। রেঞ্জ কুয়েরিতে কাজ করে না।
নোট: এটি মূলত MEMORY স্টোরেজ ইঞ্জিনে ব্যবহৃত হয়।
📝 Full-Text Index
ব্লগ পোস্ট, আর্টিকেল বা বড় টেক্সট সার্চ করার জন্য।
LIKE '%word%' এর চেয়ে এটি অনেক গুণ ফাস্ট এবং স্মার্ট (Natural Language Search সাপোর্ট করে)।
🗺️ Spatial Index
ম্যাপ বা লোকেশন ডেটার জন্য (Uber/Pathao স্টাইল অ্যাপে লাগে)।
৮.৩ ইনডেক্স তৈরি ও ডিলিট করা
Single Index (এক কলাম):
CREATE INDEX idx_email ON users(email);Unique Index (ডুপ্লিকেট আটকাবে + ফাস্ট সার্চ):
CREATE UNIQUE INDEX idx_phone ON users(phone);Drop Index (ইনডেক্স মুছে ফেলা):
DROP INDEX idx_email ON users;💡 Senior Tip: Primary Key, Unique Key এবং Foreign Key-এর ওপর MySQL অটোমেটিক ইনডেক্স তৈরি করে। এগুলো ম্যানুয়ালি ইনডেক্স করার দরকার নেই।
৮.৪ Composite Index (আসল পাওয়ার হাউস)
যখন আমরা একাধিক কলাম মিলিয়ে ইনডেক্স করি, তাকে Composite Index বলে।
CREATE INDEX idx_city_status ON users (city, status);এখানে ডেটাবেস প্রথমে city দিয়ে সাজাবে, তারপর status দিয়ে।
৮.৫ Left-most Prefix Rule (ইন্টারভিউতে আসবেই)
কম্পোজিট ইনডেক্স (col1, col2, col3) ব্যবহারের একটি গোল্ডেন রুল আছে। ইনডেক্সটি বাম দিক থেকে কাজ শুরু করে। মাঝখান থেকে কাজ করতে পারে না।
Index: (city, status, age)
| Query Condition | Index কাজ করবে? | কেন? |
|---|---|---|
| WHERE city = 'Dhaka' | ✅ Yes | বাম পাশের প্রথম কলাম মিলেছে। |
| WHERE city = 'Dhaka' AND status = 'active' | ✅ Yes | বাম পাশের ১ ও ২ নম্বর কলাম মিলেছে। |
| WHERE city = 'Dhaka' AND status = 'active' AND age > 20 | ✅ Yes | সব সিরিয়াল ঠিক আছে। |
| WHERE status = 'active' | ❌ NO | প্রথম কলাম (city) নেই, তাই ইনডেক্স ব্রেক করেছে। |
| WHERE city = 'Dhaka' AND age > 20 | ⚠️ Partial | শুধু city এর জন্য ইনডেক্স কাজ করবে, age এর জন্য করবে না (কারণ মাঝখানের status মিসিং)। |
৮.৬ Selectivity এবং Cardinality (কখন ইনডেক্স কাজ করে না)
সব কলামে ইনডেক্স বানালেই লাভ হয় না।
১. Index Selectivity
ডেটা কত দ্রুত ফিল্টার করা যায়।
Gender (Male/Female): মাত্র ২টি অপশন। এখানে ইনডেক্স দিলে লাভ নেই। কারণ MySQL-কে টেবিলের ৫০% ডেটা এমনিতেই পড়তে হবে। একে বলে Low Selectivity।
Email/Phone: সবাই ইউনিক। এখানে ইনডেক্স সেরা পারফর্ম করে।
২. Index Cardinality
কতগুলো ইউনিক ভ্যালু আছে।
SHOW INDEX FROM users;Cardinality ভ্যালু যত বেশি, ইনডেক্স তত ভালো কাজ করবে।
Cardinality কম হলে (যেমন status কলামে মাত্র ৩টি ভ্যালু - active, inactive, pending), সেখানে ইনডেক্স খুব একটা ফায়দা দেয় না।
৮.৭ MySQL কখন INDEX ব্যবহার করবে না? (Silent Killer)
আপনি ইনডেক্স বানিয়েছেন, কিন্তু কুয়েরি স্লো। কারণ নিচের ভুলগুলো করলে MySQL ইনডেক্স ইগনোর করে:
❌ Wildcard at start:
LIKE '%shagor' -- (শুরুতে % থাকলে পুরো টেবিল স্ক্যান হয়)✅ সমাধান: LIKE 'shagor%' (শেষে % থাকলে ইনডেক্স কাজ করে)।
❌ Functions on Column:
WHERE YEAR(created_at) = 2024 -- (ফাংশন ব্যবহারের কারণে ইনডেক্স নষ্ট)✅ সমাধান: WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
❌ Data Type Mismatch:
phone কলাম যদি VARCHAR হয় আর আপনি কুয়েরি করেন WHERE phone = 017... (Number), তাহলে ইনডেক্স কাজ করবে না। অবশ্যই WHERE phone = '017...' দিতে হবে।
❌ OR Operator:
যদি WHERE col1 = 'A' OR col2 = 'B' দেন এবং দুটিতেই ইনডেক্স না থাকে, তবে ইনডেক্স কাজ করবে না।
৮.৮ EXPLAIN — ইনডেক্সের ডাক্তার
আপনার কুয়েরি ইনডেক্স ব্যবহার করছে কি না, তা বোঝার একমাত্র উপায় EXPLAIN কিওয়ার্ড।
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';রেজাল্ট এনালাইসিস:
- type: যদি ALL লেখা থাকে, তার মানে বিপদ! ফুল টেবিল স্ক্যান হচ্ছে। যদি ref বা range বা const থাকে, তার মানে ইনডেক্স কাজ করছে।
- key: কোন ইনডেক্সটি ব্যবহার হয়েছে তার নাম দেখাবে।
- rows: কতগুলো রো স্ক্যান করে রেজাল্ট পেয়েছে। এই সংখ্যা যত কম, তত ভালো।
৮.৯ Real-World Example (Before vs After)
Scenario: ৫ মিলিয়ন অর্ডারের টেবিল থেকে নির্দিষ্ট ইউজারের অর্ডার বের করা।
❌ Before Index:
SELECT * FROM orders WHERE user_id = 5500;
-- Time: 2.5 seconds
-- Rows scanned: 5,000,000 (ALL)✅ After Index:
CREATE INDEX idx_user ON orders(user_id);
SELECT * FROM orders WHERE user_id = 5500;
-- Time: 0.02 seconds
-- Rows scanned: 15 (REF)৮.১০ JOIN Optimization এবং Index
Golden Rule: যেই কলাম দিয়ে দুটি টেবিল JOIN করছেন (সাধারণত Foreign Key), সেই কলামে অবশ্যই ইনডেক্স থাকতে হবে।
-- orders.user_id এবং users.id দুটিতেই ইনডেক্স থাকতে হবে
SELECT * FROM orders JOIN users ON orders.user_id = users.id;ইনডেক্স না থাকলে এটি Nested Loop Join করবে যা অত্যন্ত ধীরগতির।
৮.১১ Senior Level Indexing Strategy (Checklist)
- ⚠️ Don't Over-Index: প্রতিটি ইনডেক্স INSERT এবং UPDATE কুয়েরিকে স্লো করে দেয় (কারণ ডেটা সেভ করার সময় ইনডেক্স ট্রি-ও আপডেট করতে হয়)। শুধু প্রয়োজনীয় কলামে ইনডেক্স দিন।
- ✓ Covering Index (Pro Tip): যদি আপনার ইনডেক্স হয় (name, email) আর আপনি কুয়েরি করেন SELECT name, email FROM users WHERE name = 'A', তাহলে MySQL মেইন টেবিলে হাতই দেবে না। সরাসরি ইনডেক্স ফাইল থেকেই ডেটা দিয়ে দেবে। এটি সবচেয়ে ফাস্ট কুয়েরি।
- ✓ Order By Limit: ORDER BY date DESC LIMIT 10 টাইপের কুয়েরিতে date কলামে ইনডেক্স থাকলে ম্যাজিকের মতো কাজ করে।
৮.১২ Full-Text Index (Search Engine Logic)
যখন LIKE দিয়ে কাজ হয় না, তখন এটি লাগে।
CREATE FULLTEXT INDEX idx_content ON articles(content);
-- সার্চ করার নিয়ম
SELECT * FROM articles
WHERE MATCH(content) AGAINST ('database optimization' IN NATURAL LANGUAGE MODE);এটি গুগল সার্চের মতো প্রাসঙ্গিক রেজাল্ট দেয় এবং অনেক ফাস্ট।
🎯 অধ্যায় ৮ এর সারাংশ (Summary)
এই অধ্যায়ে আপনি শিখলেন:
- ✓ Index Concept: বি-ট্রি স্ট্রাকচার এবং বাইনারি সার্চ।
- ✓ Composite Index: লেফট-মোস্ট প্রিফিক্স রুল (১০০% ইন্টারভিউ প্রশ্ন)।
- ✓ Optimization: কার্ডিনালিটি এবং সিলেক্টিভিটি চেক করা।
- ✓ Debugging: EXPLAIN দিয়ে কুয়েরি চেক করা।
- ✓ Pitfalls: ফাংশন, ওয়াইল্ডকার্ড এবং টাইপ মিসম্যাচ এর কারণে কখন ইনডেক্স কাজ করে না।
✨ ইনডেক্সিং হলো পারফরম্যান্স অপটিমাইজেশনের হার্ট। এই চ্যাপ্টারটি মাস্টারি করতে পারলে আপনি যেকোনো স্লো সিস্টেমকে ফাস্ট করতে পারবেন।
পরবর্তী অধ্যায়ে আমরা শিখব Query Optimization, Stored Procedure, Triggers এবং Views—যেখানে আমরা ইনডেক্স ছাড়াও কুয়েরি রি-রাইট এবং আর্কিটেকচারাল অপটিমাইজেশন নিয়ে কথা বলব।
প্রস্তুত তো? 🚀