"use strict"; const {Fragment: _Fragment, jsx: _jsx, jsxs: _jsxs} = arguments[0]; const {useMDXComponents: _provideComponents} = arguments[0]; function _createMdxContent(props) { const _components = { code: "code", h3: "h3", li: "li", ol: "ol", p: "p", pre: "pre", span: "span", strong: "strong", table: "table", tbody: "tbody", td: "td", th: "th", thead: "thead", tr: "tr", ul: "ul", ..._provideComponents(), ...props.components }, {ComparisonTable, Definition, English, Explanation, Hinglish, ImportantQA, MCQ, SubTopicHeader, Summary} = _components; if (!ComparisonTable) _missingMdxReference("ComparisonTable", true); if (!Definition) _missingMdxReference("Definition", true); if (!English) _missingMdxReference("English", true); if (!Explanation) _missingMdxReference("Explanation", true); if (!Hinglish) _missingMdxReference("Hinglish", true); if (!ImportantQA) _missingMdxReference("ImportantQA", true); if (!MCQ) _missingMdxReference("MCQ", true); if (!SubTopicHeader) _missingMdxReference("SubTopicHeader", true); if (!Summary) _missingMdxReference("Summary", true); return _jsxs(_Fragment, { children: [_jsx(SubTopicHeader, { id: "3.3", title: "Database Indexes & Query Performance Tuning" }), "\n", _jsxs(Definition, { term: "Database Index", children: [_jsx(English, { children: _jsx(_components.p, { children: "A Database Index is a physical data structure (usually a B-Tree) that the DBMS uses to locate rows in a table quickly, without scanning the entire table." }) }), _jsx(Hinglish, { children: _jsx(_components.p, { children: "Database Index ek physical data structure (mostly B-Tree) hota hai jiska use DBMS table mein se specific records ko jaldi search karne ke liye karta hai, bina poore table ko scan kiye." }) })] }), "\n", _jsxs(Explanation, { children: [_jsxs(English, { children: [_jsx(_components.h3, { children: "Clustered vs. Non-Clustered Indexes" }), _jsx(_components.p, { children: "Indexes are physically organized in two main ways:" }), _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Clustered Index:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "Determines the physical storage order of the data rows in a table based on the key values." }), "\n", _jsxs(_components.li, { children: ["A table can have ", _jsx(_components.strong, { children: "only one" }), " clustered index (usually on the Primary Key) because data rows can be physically sorted in only one order."] }), "\n", _jsx(_components.li, { children: "Key values are stored at the leaf nodes directly with the actual data rows." }), "\n"] }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Non-Clustered Index:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "Has a structure separate from the data rows. An index entry contains the key values and pointers (Row IDs) to the actual data rows." }), "\n", _jsxs(_components.li, { children: ["A table can have ", _jsx(_components.strong, { children: "multiple" }), " non-clustered indexes."] }), "\n", _jsx(_components.li, { children: "Similar to an index page at the back of a textbook." }), "\n"] }), "\n"] }), "\n"] })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "Clustered vs. Non-Clustered Indexes" }), _jsx(_components.p, { children: "Indexes do mukhiya tarike se store hote hain:" }), _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Clustered Index:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "Table ke actual data rows ke physical storage order ko define karta hai." }), "\n", _jsxs(_components.li, { children: ["Ek table mein ", _jsx(_components.strong, { children: "sirf ek" }), " clustered index ho sakta hai (generally Primary Key par) kyunki data ko physical memory mein sirf ek hi order mein sort kiya ja sakta hai."] }), "\n", _jsx(_components.li, { children: "Index ke leaf nodes par actual data rows stored hoti hain." }), "\n"] }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Non-Clustered Index:" }), "\n", _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "Iska structure table ke physical data se alag hota hai. Index ke node par key value aur actual row ka physical address (pointer/RID) hota hai." }), "\n", _jsxs(_components.li, { children: ["Ek table par ", _jsx(_components.strong, { children: "multiple" }), " non-clustered indexes banaye ja sakte hain."] }), "\n", _jsx(_components.li, { children: "Jaise kisi book ke piche ka index page." }), "\n"] }), "\n"] }), "\n"] })] }), _jsx(ComparisonTable, { children: _jsxs(_components.table, { children: [_jsx(_components.thead, { children: _jsxs(_components.tr, { children: [_jsx(_components.th, { style: { textAlign: "left" }, children: "Feature" }), _jsx(_components.th, { style: { textAlign: "left" }, children: "Clustered Index" }), _jsx(_components.th, { style: { textAlign: "left" }, children: "Non-Clustered Index" })] }) }), _jsxs(_components.tbody, { children: [_jsxs(_components.tr, { children: [_jsx(_components.td, { style: { textAlign: "left" }, children: _jsx(_components.strong, { children: "Physical Order" }) }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Alters physical storage order of table data" }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Independent of data storage order" })] }), _jsxs(_components.tr, { children: [_jsx(_components.td, { style: { textAlign: "left" }, children: _jsx(_components.strong, { children: "Count per Table" }) }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Maximum 1" }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Multiple (up to 999 or unlimited)" })] }), _jsxs(_components.tr, { children: [_jsx(_components.td, { style: { textAlign: "left" }, children: _jsx(_components.strong, { children: "Leaf Node Content" }) }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Actual data rows" }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Pointer to the data row (RID/Clustered Key)" })] }), _jsxs(_components.tr, { children: [_jsx(_components.td, { style: { textAlign: "left" }, children: _jsx(_components.strong, { children: "Query Speed" }) }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Extremely fast for range scans" }), _jsx(_components.td, { style: { textAlign: "left" }, children: "Fast for single point lookups, extra lookup jump" })] })] })] }) }), _jsxs(English, { children: [_jsx(_components.h3, { children: "Index Structures: B-Tree and Hash" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "B-Tree Index (Balanced Tree):" }), " Self-balancing tree structure where search, insertion, and deletion can be done in logarithmic time (", _jsx(_components.span, { className: "katex", children: _jsx(_components.span, { className: "katex-html", "aria-hidden": "true", children: _jsxs(_components.span, { className: "base", children: [_jsx(_components.span, { className: "strut", style: { height: "1em", verticalAlign: "-0.25em" } }), _jsx(_components.span, { className: "mord mathnormal", style: { marginRight: "0.0278em" }, children: "O" }), _jsx(_components.span, { className: "mopen", children: "(" }), _jsxs(_components.span, { className: "mop", children: ["lo", _jsx(_components.span, { style: { marginRight: "0.0139em" }, children: "g" })] }), _jsx(_components.span, { className: "mspace", style: { marginRight: "0.1667em" } }), _jsx(_components.span, { className: "mord mathnormal", children: "n" }), _jsx(_components.span, { className: "mclose", children: ")" })] }) }) }), "). Ideal for equality searches and range queries (e.g., ", _jsx(_components.code, { children: "BETWEEN" }), ", ", _jsx(_components.code, { children: ">" }), ", ", _jsx(_components.code, { children: "<" }), ")."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Hash Index:" }), " Uses a hash function to map key values to physical bucket locations. Extremely fast for exact equality searches (", _jsx(_components.code, { children: "=" }), "), but useless for range queries or sorting because hashed values are randomly distributed."] }), "\n"] })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "Index Structures: B-Tree aur Hash" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "B-Tree Index (Balanced Tree):" }), " Yeh ek self-balancing tree structure hai jisme search aur write operations logarithmic time (", _jsx(_components.span, { className: "katex", children: _jsx(_components.span, { className: "katex-html", "aria-hidden": "true", children: _jsxs(_components.span, { className: "base", children: [_jsx(_components.span, { className: "strut", style: { height: "1em", verticalAlign: "-0.25em" } }), _jsx(_components.span, { className: "mord mathnormal", style: { marginRight: "0.0278em" }, children: "O" }), _jsx(_components.span, { className: "mopen", children: "(" }), _jsxs(_components.span, { className: "mop", children: ["lo", _jsx(_components.span, { style: { marginRight: "0.0139em" }, children: "g" })] }), _jsx(_components.span, { className: "mspace", style: { marginRight: "0.1667em" } }), _jsx(_components.span, { className: "mord mathnormal", children: "n" }), _jsx(_components.span, { className: "mclose", children: ")" })] }) }) }), ") mein hote hain. Range queries (e.g., ", _jsx(_components.code, { children: "BETWEEN" }), ", ", _jsx(_components.code, { children: ">" }), ", ", _jsx(_components.code, { children: "<" }), ") aur equality search dono ke liye ideal hai."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Hash Index:" }), " Hash function ke through key values ko buckets mein map karta hai. Exact matching (", _jsx(_components.code, { children: "=" }), ") ke liye bahut fast hai par range queries ya sorting ke liye bekar hai kyunki hash values random order mein generate hoti hain."] }), "\n"] })] }), _jsxs(English, { children: [_jsx(_components.h3, { children: "SQL Index Syntax" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Create a Unique Index:" }), "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "CREATE UNIQUE INDEX idx_student_email ON Student (Email);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Create a Composite Index (on multiple columns):" }), "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "CREATE INDEX idx_student_branch_age ON Student (BranchCode, Age);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Drop an Index:" }), "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DROP INDEX idx_student_email; -- Syntax varies (e.g. DROP INDEX idx ON Table in MySQL)\n" }) }), "\n"] }), "\n"] }), _jsx(_components.h3, { children: "Query Optimization and Tuning" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Query Optimizer:" }), " The DBMS component that analyzes SQL statements and determines the most efficient ", _jsx(_components.strong, { children: "Query Execution Plan" }), " (e.g., deciding whether to do a Full Table Scan or Index Scan)."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Execution Plan:" }), " A roadmap showing how the database executes a query. In SQL, it is accessed using ", _jsx(_components.code, { children: "EXPLAIN" }), ":", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "EXPLAIN SELECT * FROM Student WHERE Email = 'amit@gmail.com';\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Tuning Best Practices:" }), "\n", _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: ["Create indexes on columns frequently used in ", _jsx(_components.code, { children: "WHERE" }), ", ", _jsx(_components.code, { children: "JOIN" }), ", and ", _jsx(_components.code, { children: "ORDER BY" }), " clauses."] }), "\n", _jsx(_components.li, { children: "Avoid indexing columns with low selectivity (e.g., Gender, where values are only Male/Female)." }), "\n", _jsxs(_components.li, { children: ["Keep tables updated with ", _jsx(_components.code, { children: "ANALYZE TABLE" }), " or ", _jsx(_components.code, { children: "VACUUM" }), " to ensure the optimizer has accurate statistics."] }), "\n"] }), "\n"] }), "\n"] })] }), _jsxs(Hinglish, { children: [_jsx(_components.h3, { children: "SQL Index Syntax" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Create a Unique Index:" }), "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "CREATE UNIQUE INDEX idx_student_email ON Student (Email);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Create a Composite Index (multiple columns):" }), "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "CREATE INDEX idx_student_branch_age ON Student (BranchCode, Age);\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Drop an Index:" }), "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "DROP INDEX idx_student_email;\n" }) }), "\n"] }), "\n"] }), _jsx(_components.h3, { children: "Query Optimization aur Tuning" }), _jsxs(_components.ul, { children: ["\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Query Optimizer:" }), " DBMS ka engine part jo query ko analyze karke sabse efficient path choose karta hai (", _jsx(_components.strong, { children: "Query Execution Plan" }), "). Yeh choose karta hai ki table scan (Full Table Scan) karna hai ya Index Scan."] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Execution Plan:" }), " Plan dekhne ke liye ", _jsx(_components.code, { children: "EXPLAIN" }), " keyword use hota hai:", "\n", _jsx(_components.pre, { children: _jsx(_components.code, { className: "language-sql", children: "EXPLAIN SELECT * FROM Student WHERE Email = 'amit@gmail.com';\n" }) }), "\n"] }), "\n", _jsxs(_components.li, { children: [_jsx(_components.strong, { children: "Tuning Tips:" }), "\n", _jsxs(_components.ol, { children: ["\n", _jsxs(_components.li, { children: ["Jo columns ", _jsx(_components.code, { children: "WHERE" }), ", ", _jsx(_components.code, { children: "JOIN" }), ", ya ", _jsx(_components.code, { children: "ORDER BY" }), " mein baar-baar use hote hain, unpar index lagayein."] }), "\n", _jsx(_components.li, { children: "Low selectivity wale columns (e.g., Gender jisme sirf Male/Female ho) par index na banayein." }), "\n", _jsx(_components.li, { children: "Regular database statistics update karein taaki optimizer sahi decision le sake." }), "\n"] }), "\n"] }), "\n"] })] })] }), "\n", _jsxs(Summary, { children: [_jsx(English, { children: _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "Indexes speed up data retrieval. Clustered index sorts physical data; Non-clustered stores pointers." }), "\n", _jsx(_components.li, { children: "B-Tree indexes handle range and equality queries; Hash indexes are for exact equality queries only." }), "\n", _jsxs(_components.li, { children: ["The Query Optimizer selects execution plans; ", _jsx(_components.code, { children: "EXPLAIN" }), " helps analyze scan types for performance."] }), "\n"] }) }), _jsx(Hinglish, { children: _jsxs(_components.ul, { children: ["\n", _jsx(_components.li, { children: "Indexes data fetch fast karte hain. Clustered index physical storage badalta hai (max 1); Non-clustered pointers rakhta hai (multiple)." }), "\n", _jsx(_components.li, { children: "B-Tree range search handle karta hai; Hash index exact matches ke liye best hai." }), "\n", _jsxs(_components.li, { children: ["Query Optimizer efficient plan select karta hai; ", _jsx(_components.code, { children: "EXPLAIN" }), " keyword queries check karne mein help karta hai."] }), "\n"] }) })] }), "\n", _jsx(ImportantQA, { questions: [{ "q": "Compare Clustered Index and Non-Clustered Index in detail.", "marks": 6, "a": { "components": [{ "type": "Explanation", "content": "1. **Count:** A table can have only one Clustered Index, but multiple Non-Clustered Indexes (typically up to 999).\n2. **Physical Storage:** Clustered index physically re-orders table rows based on the index key. Non-Clustered index does not alter the physical storage order; it maintains a separate index structure.\n3. **Leaf Nodes:** Clustered index leaf nodes contain the actual table data rows. Non-Clustered leaf nodes contain row pointers (Row IDs or clustered keys) pointing to the physical records.\n4. **Speed:** Clustered is faster for range queries since rows are contiguous. Non-Clustered is slightly slower due to the overhead of following pointers to access actual rows.1. **Sankhya (Count):** Ek table mein sirf 1 Clustered index ho sakta hai, jabki Non-Clustered index multiple ho sakte hain.\n2. **Physical Storage:** Clustered index table ke actual data rows ko physically sort karke save karta hai. Non-Clustered index rows ki physical arrangement ko touch nahi karta aur alag mapping file banata hai.\n3. **Leaf Nodes:** Clustered index ke leaf nodes par actual database records hote hain. Non-Clustered ke leaf nodes par physical pointers (Row IDs) hote hain.\n4. **Speed:** contiguous storage ki wajah se Clustered range search mein fast hota hai. Non-Clustered point lookup ke liye fast hai par extra pointer jump ki wajah se thoda slow ho jata hai." }] } }, { "q": "Write SQL queries to:\n1. Create a unique index on 'PanNumber' in table 'Customer'.\n2. Drop an index named 'idx_email'.", "marks": 4, "a": { "components": [{ "type": "Explanation", "content": "1. Create Unique Index:\n```sql\nCREATE UNIQUE INDEX idx_cust_pan ON Customer (PanNumber);\n```\n2. Drop Index:\n```sql\nDROP INDEX idx_email;\n```1. Create Unique Index:\n```sql\nCREATE UNIQUE INDEX idx_cust_pan ON Customer (PanNumber);\n```\n2. Drop Index:\n```sql\nDROP INDEX idx_email;\n```" }] } }] }), "\n", _jsx(MCQ, { questions: [{ "id": "2ee52e96-6861-4a4e-89c2-989718eb096a", "q": "How many Clustered Indexes can be created on a single database table?", "options": ["Unlimited", "Maximum 1", "Up to 255", "None"], "answer": 1, "hint": "Data rows on disk can only be physically sorted in one way.", "explanation": "A table can only have one clustered index because the physical storage order of the data can only be sorted in a single order." }, { "id": "00599011-b3f4-43ec-aa10-9559bce420bf", "q": "Where does the leaf node of a Clustered Index point to?", "options": ["To another non-clustered index", "To row identifiers (pointers) on disk", "Directly to the actual data rows of the table", "To the database transaction logs"], "answer": 2, "hint": "Clustered indexes incorporate the table data in their leaves.", "explanation": "In a clustered index, the leaf nodes contain the actual data rows of the table." }, { "id": "4c343343-ca29-41a0-ab75-5d387c0f1bc6", "q": "Which data structure is most commonly used for database indexes to support range queries?", "options": ["Hash Table", "B-Tree", "Linked List", "Stack"], "answer": 1, "hint": "It is a balanced tree structure.", "explanation": "B-Tree (and B+ Tree) structures are the industry standard for database indexing as they keep keys sorted and allow fast range lookups." }, { "id": "127bd392-0fe7-460a-891a-b87e0964834c", "q": "What is the command syntax to view the execution plan chosen by the query optimizer?", "options": ["VIEW PLAN SELECT ...", "EXPLAIN SELECT ...", "SHOW QUERY SELECT ...", "ANALYZE PLAN SELECT ..."], "answer": 1, "hint": "Think of asking the database to explain how it will run the query.", "explanation": "The EXPLAIN statement displays the query execution plan, showing scan types, join orders, and index usage." }, { "id": "d58413e5-601c-450e-a653-8d456c69e800", "q": "Which type of query benefits the LEAST from a B-Tree index?", "options": ["SELECT * FROM Student WHERE Age BETWEEN 18 AND 22;", "SELECT * FROM Student WHERE Name LIKE '%kumar';", "SELECT * FROM Student WHERE RollNo = 105;", "SELECT * FROM Student WHERE Age > 20;"], "answer": 1, "hint": "If the wildcard '%' is at the start of a LIKE pattern, the index cannot be searched from the root.", "explanation": "A leading wildcard ('%kumar') prevents the B-Tree index from performing tree traversal, forcing a full scan. Range operations and exact matches use the index fully." }, { "id": "fc373de7-0b01-4141-995b-477b31a24dcf", "q": "What is a major disadvantage of creating too many indexes on a single table?", "options": ["It makes SELECT queries slower", "It slows down INSERT, UPDATE, and DELETE operations", "It disables primary keys", "It deletes data dictionary metadata"], "answer": 1, "hint": "Every time data is inserted or updated, the indexes must also be updated.", "explanation": "While indexes speed up read (SELECT) operations, they slow down write (INSERT/UPDATE/DELETE) operations because all index structures must be updated." }, { "id": "b60a193f-89db-4940-ab89-a83d221e1436", "q": "An index created on more than one column of a table is called a:", "options": ["Clustered Index", "Unique Index", "Composite Index", "Multi-table Index"], "answer": 2, "hint": "Composite means made of multiple parts.", "explanation": "An index on multiple attributes is called a Composite Index (or Compound Index)." }, { "id": "b4802c4a-2de8-4f2c-8994-2c088f44219c", "q": "Which index structure is best suited for exact match queries (e.g. WHERE ID = 10) but does not support range scans?", "options": ["B-Tree Index", "Hash Index", "Bitmap Index", "Clustered Index"], "answer": 1, "hint": "It uses a key-to-bucket hashing mechanism.", "explanation": "Hash indexes utilize hash tables, which offer O(1) time complexity for exact matches but do not support range searches or sorting." }, { "id": "f9229d0e-a77b-4e33-ac0c-1d9ef4d7c404", "q": "What does a 'Full Table Scan' in an execution plan indicate?", "options": ["The query executed successfully using a clustered key", "The database checked every single row in the table from top to bottom", "The query took 0 milliseconds to execute", "The table has been completely backed up"], "answer": 1, "hint": "Scan means going through everything.", "explanation": "A Full Table Scan (or Sequential Scan) means the database engine reads every row in the table to find matches, indicating a lack of suitable index." }, { "id": "4b851284-e9be-46dd-a64c-39aad11c7540", "q": "Which column would be the WORST candidate for creating an index?", "options": ["Email", "AadhaarNumber", "Gender (Male/Female)", "EmployeeID"], "answer": 2, "hint": "Low selectivity columns contain very few distinct values.", "explanation": "Gender has low selectivity (cardinality). Indexing columns with very few distinct values is ineffective, as the optimizer will choose table scans anyway." }], topicId: "3.3" })] }); } function MDXContent(props = {}) { const {wrapper: MDXLayout} = { ..._provideComponents(), ...props.components }; return MDXLayout ? _jsx(MDXLayout, { ...props, children: _jsx(_createMdxContent, { ...props }) }) : _createMdxContent(props); } return { default: MDXContent }; function _missingMdxReference(id, component) { throw new Error("Expected " + (component ? "component" : "object") + " `" + id + "` to be defined: you likely forgot to import, pass, or provide it."); }