Introduction
👉Subscribe to our new channel:www.youtube.com/@varunainashots
👉Link for DBMS Notes:
đź”—File 1: rb.gy/8g186
đź”—File 2: rb.gy/s7l18
🧑‍🎓Contributed by: Anurag Singhal
This video explains Why we need indexing from very basics. Concept of indexing is very important for GATE UGC NET and specially Interviews.
DBMS Notes:
File 1: drive.google.com/file/d/14bXNQ8BRqYj_F5Q0h9kjCu5dBV_-Yc3h/view
File 2: drive.google.com/file/d/1O4KdWod2jd3ja5kK62Fskqst-Wkz80R4/view
Contributed by: Anurag Singhal
â–şDatabase Management System(Complete Playlist):
www.youtube.com/playlist
Other subject-wise playlist Links:
--------------------------------------------------------------------------------------------------------------------------------------
â–şDesign and Analysis of algorithms (DAA):
www.youtube.com/playlist
â–şComputer Architecture (Complete Playlist):
www.youtube.com/playlist
â–ş Theory of Computation
www.youtube.com/playlist
â–şArtificial Intelligence:
www.youtube.com/playlist
â–şComputer Networks (Complete Playlist):
www.youtube.com/playlist
â–şOperating System:
www.youtube.com/playlist
â–şStructured Query Language (SQL):
www.youtube.com/playlist
â–şDiscrete Mathematics:
www.youtube.com/playlist
â–şCompiler Design:
www.youtube.com/playlist
â–şNumber System:
www.youtube.com/playlist
â–şCloud Computing & BIG Data:
www.youtube.com/playlist
â–şSoftware Engineering:
www.youtube.com/playlist
â–şData Structure:
www.youtube.com/playlist
â–şGraph Theory:
www.youtube.com/playlist
â–şProgramming in C:
www.youtube.com/playlist
â–şDigital Logic:
www.youtube.com/playlist
---------------------------------------------------------------------------------------------------------------------------------------
Our social media Links:
â–ş Subscribe to us on YouTube: www.youtube.com/gatesmashers
â–şSubscribe to our new channel: www.youtube.com/@varunainashots
â–ş Like our page on Facebook: www.facebook.com/gatesmashers
â–ş Follow us on Instagram: www.instagram.com/gate.smashers
â–ş Follow us on Instagram: www.instagram.com/varunainashots
â–ş Follow us on Telegram: t.me/gatesmashersofficial
--------------------------------------------------------------------------------------------------------------------------------------
â–şFor Any Query, Email us at: gatesmashers2018@gmail.com
â–şBe a Member & Give your Support on the below link: www.youtube.com/channel/UCJihyK0A38SZ6SdJirEdIOw/join
Content
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 favourite topic But here how indexing works and what it is and what's is use are later topics We from basic architecture, I'm starting this topic from very basic that why the indexing is actually used? and before that We'll talk about architecture and on which scenario we're talking In.
This video I'll, firstly clear the scenario that what is scenario in actual Where does indexing exist.
So, we'll start the video here I've in architecture, general architecture in our laptop and computer and in our devices That we've CPU CPU, which is a processor.
We call it processor, which has to process Query comes to CPU ad.
We talk in term of Database So in Database.
We call query, means a question is coming that select star from the table.
This find the name of the student whose age is this Find the detail of student, whose roll number is this Like.
This way there are many different queries.
So.
Those queries are handle and processed by CPU Now.
If a query is coming, A simple query select * from student table where Roll number is equal to 1, I've written a basic query, select * from the student, where Roll no is 1 Mean from student table find out the detail of Roll number 1 means its name, mother name, , father, name, age, gender, marks anything whatever in database.
Its line and record take out the whole record Now.
This query is execute by CPU Is.
Data of the student table is with CPU?.
No,Data is saved in memory and there are many types of memory, but in general architecture we consider to use 2 memories.
First, one is RAM and second is secondary memory.
Ram volatile means RAM directly work with CPU, because CPU speed is generally in MIPS ( Million instruction per second ) and Hard disk is very slow as compare to CPU Because CPU speed and Hard disk speed is not compatible with each other So.
The data is in hard disk,why ?, because we want to store data permanently, We, don't to temporary store data.
If whole student table is on RAM If you shut down the system, Table is lost, data is lost so where to keep the data, we always save the data permanent in hard disk, so data is here.
Cpu is here so when CPU will execute this query, So will CPU interact with hard disk No.
It will not because CPU speed is in MIPS and hard disk speed is very slow.
This is million instruction per second, and that is 10-10 instruction per second, and if we see generally its speed, is we generally count in milliseconds and CPU speed is works in nanosecond or picoseconds, so its speed is not compatible with each other.
So the data that we've here In this way- Like Roll number 1, father name and something Age.
Gender male In, this way we've lot of data and records and how generally we keep the records, first, you see that hard disk In hard disk.
Whatever memory we're using, we divide hard disk in blocks.
Logical blocks Not in physical blocks.
In you laptop, also C drive, D drive, E drive, F drive are made.
These all are logical, drives So, you can manage data, but at back end Storage manager, whole operating system, whole hard drive in fixed size, blocks, divide and then insert the data.
Records in this way will get insert Now here you've to understand that data is here and CPU is here: CPU don't interact directly.
Cpu will tell RAM that I need data.
Ram will call the data from secondary memory from secondary memory.
Here blocks- and you can say pages, also made In operating system term.
We instead of blocks called it pages.
That we've divided secondary memory in pages, We are considering general architecture, so you can call it block or page Block means a fixed size, box or boxes, and collection of boxes is called Hard Disk and in that blocks there is records If.
We see a general example: If I've 10,000 numbers of record 10,000 numbers of record Means in a student table.
There is record of 10,000 entries Data of 10,000 students and, if size of 1 block I've means 1 block can store 100 records.
If it is given, 10,000 blocks are there and every block has size of 100 record means.
1 block can store 100 records, so how many blocks are required? Number of blocks required How you'll calculate 10,000 divide by 100, so this is 100 means you need 100 blocks Means.
You need 100 blocks to store all this data, and here we've CPU, RAM and hard disk Now see how it works.
If, CPU found student detail from student table found roll number 1.
Now data can be inserted in 2 ways sorted or unsorted.
It can ordered or unordered So how to call data if I think that data is non ordered non ordered means there is no order.
Like roll number, 1, 10, 20, 100 200 300 5,6 in such way data is there So when we call this data, how it works.
Firstly one block Whole block Comes to a RAM means this whole block will go to RAM.
There will be searching.
If you got the answer, then it is good and if don't then it is a miss means, hit or miss and we'll send that block back and then call for another block and return and again for 3rd block and return.
Then 4 rd block and return like this way blocks are called in RAM searched.
If you get that it is hit and if don't then miss and returned back so here, indexing is used.
We're, not reducing this searching time means time taken by 1 block time taken to search in a block indexing, don't reduce it.
In actual indexing, tha number of blocks, you're calling We call it input and output.
I/O cost.
Obviously, I/O cost you're transferring the data Transferring data from hard disk to RAM Now.
Obviously there will be some cost of transfer, There, we've, propagation and transmission time you're calling data.
We call this I/O cost So more number of blocks, you call I/o cost will be more I/o cost will be more So.
Time will keep on increasing.
So what we've to bring in actual we've to bring such method that I've call lesser number of blocks.
This is the main motive.
I'll give example of a book I've a DBMS books lets say Navathe.
If there is 1000 pages in that book and you've to search a topic, let's say you've to search indexing, how you'll do if there is not index there, You'll scroll, single page and search, so how many pages you've to serach in worst case may you've to search all 1000 pages and in best case may be you get data on first page only means on a average.
You can say 500, because if I consider average case you've to check 500 pages, but if you use index, if you use index Your number of pages you are shuffling will get reduce because index is only of 5-6 pages fisrt.
You see topic in Index from that topic.
You can go to that page and from that page you can search data directly.
So in this video I only want to tell you this and what indexing do in actual Indexing number of blocks.
We are calling it reduce them.
So I/o cost is reduced, I/o cost is reduced by indexing.
This is the advantage of of indexing, so how it works and what it is that we'll see in further video Here I've only told you about basic introduction of indexing.
Thank you.
FAQs
Why is indexing used? ›
Indexes are used to quickly locate data without having to search every row in a database table every time said table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
What is indexing and when should you use it? ›Indexing is the way to get an unordered table into an order that will maximize the query's efficiency while searching. When a table is unindexed, the order of the rows will likely not be discernible by the query as optimized in any way, and your query will therefore have to search through the rows linearly.
Why indexing is used in DBMS? ›What is Indexing in DBMS? Indexing is a technique for improving database performance by reducing the number of disk accesses necessary when a query is run. An index is a form of data structure. It's used to swiftly identify and access data and information present in a database table.
Why is data indexing important? ›Indexes are a powerful tool used in the background of a database to speed up querying. Indexes contain all the necessary information needed to access items quickly and efficiently. Indexes serve as lookup tables to efficiently store data for quicker retrieval. Table keys are stored in indexes.
Where is indexing used? ›Indexing is used to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed. The index is a type of data structure. It is used to locate and access the data in a database table quickly.
What are the basic rules of indexing? ›- Equality.
- Ordering.
- Approximate.
- Substring.
- Reverse.
In analyzing subject content and selecting concepts, indexers should always bear in mind three important principles of the indexing process: exhaustivity, specificity and consistency.
What are the three stages of indexing? ›Stage 1: Queuing URLs for Indexing. Stage 2: Crawling URLs. Stage 3: Updating the Index.
What are the 2 main types of indexing? ›Primary indexing is divided into two types, dense and sparse. Dense indexing is used when the index table contains records for every search key. Sparse indexing is used when the index table does not use a search key for every record.
What is indexing and how does it work? ›What is indexing? Indexing is a way of sorting a number of records on multiple fields. Creating an index on a field in a table creates another data structure which holds the field value, and a pointer to the record it relates to. This index structure is then sorted, allowing Binary Searches to be performed on it.
Should I turn Windows indexing on or off? ›
Keeping the Search Indexer running is recommended, as it makes Windows searching easier. However, this service sometimes consumes more RAM than is generally required. In such situations, you may encounter memory issues on your Windows computer.
What is an example of indexing? ›Investment markets
Indexing can also support investors and financial analysts in making investment decisions. The Dow Jones Industrial Average (DJIA) and Standard & Poor's 500 (S&P 500) are examples of investment indexes.
Indexes should not be used on small tables. Indexes should not be used on columns that return a high percentage of data rows when used as a filter condition in a query's WHERE clause. For instance, you would not have an entry for the word "the" or "and" in the index of a book.
What is indexing short answer? ›In general, indexing refers to the organization of data according to a specific schema or plan. In IT, the term has various similar uses including, among other things, making information more presentable and accessible.
What happens if you turn off indexing? ›Disabling indexing will increase the time it takes for Windows and other apps to return search results. So, if you have a fast CPU and a standard hard drive, you can keep indexing on. Since hard drives are slow to read, Windows will take longer searching for files without indexed data.
How long should Windows indexing take? ›When you search your PC after indexing, it looks at an index of terms to find results faster. When you first run indexing, it can take up to a couple hours to complete. After that, indexing will run in the background on your PC as you use it, only re-indexing updated data.
Does indexing slow down computer? ›Even if you have an SSD disk, turning off indexing can improve your speed, because the constant writing to disk that indexing does can eventually slow down SSDs. To get the maximum benefit in Windows 10, you need to turn indexing off completely.
What are the three types of indexing? ›- Unique indexes enforce the constraint of uniqueness in your index keys.
- Bidirectional indexes allow for scans in both the forward and reverse directions.
- Clustered indexes can help improve the performance of queries that traverse the table in key order.
The B-tree index is the most common type of index. It is used to search for equality and range comparisons in columns that can be sorted.
What files should not be indexed? ›An unnecessarily large or complex index could affect system performance. Examples of files that probably should not have their contents indexed include binary files, database files, and other similar files. A Windows server administrator can define file types whose contents will be excluded from the FTS index.
What are the disadvantages of indexing? ›
The first and perhaps most obvious drawback of adding indexes is that they take up additional storage space. The exact amount of space depends on the size of the table and the number of columns in the index, but it's usually a small percentage of the total size of the table.
Is indexing good or bad? ›They have various advantages like increased performance in searching for records, sorting records, grouping records, or maintaining a unique column. Some of the disadvantages include increased disk space, slower data modification, and updating records in the clustered index.