Applying Index happens in memory or disc?

All we need is an easy explanation of the problem, so here it is.

Say I have 10k records in Customer table (MySQL or oracle) which has 10 columns. One is ID(primary key) and another is name(created index on it)

Now I search for word customer whose name is Alex. My understanding is first DBMS will determine the row location for record name Alex and then fetch those
rows in memory or it is other way round(where rows are fetched in memory then indexes are applied ?)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Study the concepts of “cache” and “block”.

In many situations, especially in databases, the data on disk is organized into “blocks”. When a block is needed (to satisfy some SELECT or UPDATE or whatever), it is requested.

Meanwhile, there is a “cache” in RAM that contains some of the blocks.

To satisfy the request for a block, the system first checks to see if the block is already in cache. If it is not, then I/O is performed to bring a copy of the block from disk into RAM. (For “persistence”, the copy on disk must be considered the “source of truth”, but let’s not get into “ACID” in this discussion.)

Once the block is in the cache in RAM, the processing proceeds.

But, the cache is of a fixed size. When there is not room in cache for a block, some block currently in cache must be pushed out of the way (and written back to disk if it is “dirty”).

So…

Case 1: Everything needed for the query is already in cache: No I/O needed.

Case 2: Some blocks are in cache: Some I/O.

So… Even if you have 10 billion records, and not nearly enough RAM to cache them, Case 2 says that the processing will (eventually, maybe in hours) finish with your query.

But… You mentioned indexing. We just talked about how an index can get you to the desired row (think “block” in this view of the processing) by touching very few blocks. With a suitable index (BTree or otherwise), the desired row can possibly be found by touching very few blocks. And those blocks might already all be in cache. Hence, very fast, perhaps less than a second, perhaps with no I/O, even with billions of rows.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply