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 ?

  • That video... It gives some good basics about how databases work, but it ends up focusing on what I mentioned in my answer as a "Columnstore". Please avoid learning about such until you have a good grasp of ordinary DB layout. – Rick James Jul 23 '17 at 21:08

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.

  • Question on case 2:- Will DBMS read the complete row(specific row after using index) with all columns from disc in memory and then use the columns present index. Right ? Question on case 3:- I think information contained in index is row location on disc. So even in this case complete row will be read from disc in memory and operation to map/return the column data will be done in memory . Is n't it? – emilly Jul 23 '17 at 6:04
  • 1
    @emilly: "Will DBMS read the complete row(specific row after using index) with all columns from disc in memory" basically yes (with some exception like blobs that might be stored elsewhere). "and then use the columns present index." unclear what you mean. The operation is complete after the first half of your sentence. "I think information contained in index is row location on disc. So even in this case complete row will ..." No. The index 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. – Solarflare Jul 23 '17 at 9:00
  • 1
    @emilly, You and Solarflare are discussing a "covering" index. I augmented my answer to bring out that term as it applies to my Case 3. – Rick James Jul 23 '17 at 14:18
  • Thanks Rick and Solarflare. To be more clear on first question is 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 ? – emilly Jul 23 '17 at 14:21
  • I added to my answer "how a row is stored". And keep in mind my answer to your companion question. – Rick James Jul 23 '17 at 14:38

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.