Say I have 10 columns in Employee table where i just need to use two colums i.e. salary and name in below query
select salary from Employee where name = 'Scott';
After going through Video from 6.30 to 8.30(2 mins), My understanding is under RDBMS database(say oracle/Mysql etc) even i have to read two columns DBMS will read complete row from disc in memory then it will filter rows based on name coumn and finally read column salary. Is that correct ?
For MySQL's InnoDB Engine...
Case 1 (slowest): No index. It will scan the entire table (all rows) to find one the row(s) with name = 'Scott' and fetchsalary` from them.
Case 2: INDEX(name). This will use the index to very efficiently the Scott row(s), then fetch the rows and get the salarys.
Case 3 (fastest): INDEX(name, salary). Now the entire query can be performed inside just the index -- without reaching over to the rest of the columns in the data. More This is called a "covering" index. More precisely: If all the columns needed for the entire SELECT are found in the INDEX, then only the index need be touched, not the data.
The Data is in one BTree, ordered by the PRIMARY KEY. An INDEX is in a different BTree, ordered by the index.
BTrees are very efficient for finding a specific row or reading consecutive rows based on the index. See the Wikipedia article on BTree. This is also a good reference: https://www.percona.com/files/presentations/percona-live/london-2011/PLUK2011-b-tree-indexes-and-innodb.pdf
For other databases, there could be differences.
How a row is stored
Case 1 (most situations): All the columns of a given table "record" (aka "row") are stored together in one contiguous set of dozens, maybe hundreds, of bytes. And this is all sitting in the same "block" along with perhaps a hundred other rows. So, when you grab any column from the row, all the rest of the columns are already readily available. Your example of an Employee table probably falls into this Case. RDBMS optimization focuses on being able to get an entire row quickly, but sacrifices in not being able to get an entire column quickly.
Case 2: In some DB implementations (including MySQL's InnoDB) big columns (TEXT and BLOB) may be stored 'off record'. That is, they are put in some other block(s). Advantages () it allows for scanning rows quickly when you don't need those bulky pieces; () typically you will fetch the bulky columns for only one row at a time.
Case 3: There is a different type of database organization, with names like "Columnstore". This pivots the data so that it is easy to get all the values for a column quickly, but loses the ability to quickly get all the columns of a single row.
Focus on Case 1, since it is very common.
INDEX(name, salary) contains: name, salary, primary key. Since you do not need anything else in your query, no additional data has to be read.
Commented
Jul 23, 2017 at 9:00
Will DBMS read the complete row(specific row after using index) with all columns from disc in memory and then works on the columns in memory as it needs to (based on query to then use the columns present index.) Right ?
Instead of relying on some dubious 3d party source, you should thoroughly review the official Oracle documentation, specifically, the Concepts Manual, more specifically, the sections on Physical Storage and Logical Storage. That said, let me make a few specific points as they relate to your question(s). First, Oracle does not really read rows from the disk. It asks the OS to return 'x' bytes of data from file 'y', beginning at offset 'z'. And the smallest number of bytes, the smallest unit of IO that it will request is the 'block'. And, it keeps a map, so it knows at what offset within a file each block is located. All blocks within a given tablespace are teh same size, with the default being 8k. A block is a logical structure understood by Oracle but meaningless to the OS that is actually doing the I/O (or more precisely, transforming the request and passing it to the storage subsystem). A block contains several rows, and the selection of individual rows, and specific columns, from the block is purely an in-memory (db buffer cache) operation.
