Why Is Indexing Used in DBMS?

TL;DR

Indexing reduces the number of disk blocks a database must transfer from hard disk to RAM to answer a query, lowering I/O cost. It does not shrink the search time inside a single block; instead, like a book's index, it lets the CPU locate records by scanning only a few index pages rather than every data block.

Transcript

Hello friends, Welcome to Gate Smashers The topic is indexing Indexing from GATE and UGC point of view or from point of view other competitive exams is very much important but it is mostly important in interviews Whenever company is coming to take interview and they ask question from Database So indexing and indexing hashing topic are very favouri... Read More

Key Insights

• Indexing exists to reduce I/O cost, which is the cost of transferring data blocks from the hard disk to RAM. Fewer blocks called means lower I/O cost and faster query response.
• The CPU never interacts directly with the hard disk because their speeds are incompatible: CPU works in nanoseconds or picoseconds (millions of instructions per second) while the hard disk is far slower.
• Data is stored permanently on the hard disk because RAM is volatile: if the system shuts down, any table held only in RAM is lost, so records are saved on disk instead.
• The hard disk is divided into fixed-size logical blocks (also called pages in OS terms), and records are inserted into these blocks by the storage manager at the back end.
• Query execution works by moving one whole block into RAM at a time, searching it, and returning a hit or miss before calling the next block, repeating until the record is found.
• Indexing does not reduce the time taken to search within a single block; it only reduces the total number of blocks that must be called from disk into RAM.
• A book analogy shows the value: without an index you may scan all 1000 pages (average 500), but a 5-6 page index points you straight to the right page.
• Block count is calculated by dividing total records by records per block: 10,000 records with 100 records per block requires 100 blocks to store all the data.

Questions & Answers

Q: Why is indexing used in a DBMS?

Indexing is used to reduce I/O cost, the cost of transferring data blocks from the hard disk to RAM. The more blocks a query has to call into RAM, the higher the I/O cost and the longer the query takes. Indexing brings a method that lets the system call fewer blocks, which reduces transfer time and speeds up query response. It is a very important topic for GATE, UGC, other competitive exams, and especially interviews.

Q: Does indexing reduce the search time inside a block?

No. Indexing does not reduce the time taken to search within a single block. What indexing actually reduces is the number of blocks you are calling from the hard disk into RAM. The searching time per block stays the same; the saving comes from transferring fewer blocks overall, which lowers the total I/O cost of answering the query.

Q: Why doesn't the CPU interact directly with the hard disk?

The CPU does not interact directly with the hard disk because their speeds are not compatible. CPU speed is measured in MIPS (millions of instructions per second) and works in nanoseconds or picoseconds, while the hard disk is very slow by comparison. Because of this mismatch, the CPU tells RAM what data it needs, and RAM calls that data from secondary memory instead.

Q: Why is data stored on the hard disk instead of RAM?

Data is stored on the hard disk because we want to keep it permanently. RAM is volatile, meaning if you shut down the system, any table or data held only in RAM is lost. The hard disk provides permanent storage, so the entire student table and its records are saved there rather than in temporary volatile memory.

Q: How is data organized on the hard disk in this architecture?

The hard disk is divided into fixed-size logical blocks, not physical ones. In operating system terms these blocks are also called pages. A block is like a fixed-size box, and the collection of boxes forms the hard disk. The storage manager divides the whole drive into these fixed-size blocks and then inserts the records into them.

Q: How do you calculate the number of blocks needed to store records?

You divide the total number of records by how many records one block can store. In the example, there are 10,000 records in a student table and one block can store 100 records. So 10,000 divided by 100 equals 100, meaning you need 100 blocks to store all of that data on the hard disk.

Q: How does a query find a record when data is unordered?

When data is non-ordered, the system brings one whole block into RAM at a time and searches it. If the record is found it is a hit; if not it is a miss, the block is sent back, and the next block is called into RAM. This repeats block by block until the record is found, which makes calling many blocks costly.

Q: How does the book index analogy explain indexing?

If a DBMS book like Navathe has 1000 pages and you search for a topic without an index, you may scroll through all 1000 pages in the worst case and about 500 on average. With an index of only 5-6 pages, you first find the topic in the index, jump to the referenced page, and read the data directly, reducing how many pages you shuffle through.

Summary & Key Takeaways

• In the general architecture, a query like 'select * from student where Roll no is 1' is processed by the CPU, but the student data lives in memory. Two memory types matter: volatile RAM that works directly with the CPU, and slower secondary memory (hard disk) that stores data permanently.

• The CPU cannot interact with the hard disk directly because their speeds are incompatible. Instead the CPU asks RAM for data, and RAM calls blocks from secondary memory. The hard disk is divided into fixed-size logical blocks, and records are inserted into these blocks.

• When data is unordered, blocks are pulled into RAM one at a time and searched for a hit or miss, which is costly. Indexing reduces the number of blocks called, lowering I/O cost. Like a book index of a few pages, it points directly to the needed data.