Indexing "QUERY PROCESSING TIME" AND factors responsible

Replies

• 

  slashfear

  Hi king,
  
  Nice questions!! So you wanna know how index works right ? so Let me explain what I know about index ....
  
  So before I go ahead and explain the concept of index first you have to know how an query is executed or processed by the database engine, So let me explain this with the table structure you have provided,
  
  EMP( empno,ename,hiredate,job,salary)
  
  when you execute a query like
  
  select * from emp where salary = 42000
  
  In this case first the SQL engine has to compile the query (identify typo basically, syntax errors) and if it is fine then it proceeds to scan the entire database memory to locate an object (table) named emp, if the object is not found then the work is done!! it displays an error message stating the table not found!! if it is there then the following is what happens:
  
  The scan device scans the entire table memory to find any bad sectors (to make sure that all the table datas are perfect), once all the table records (rows and columns) are scanned the scan device moves to the starting of the table that's the first record in the table and starts to scan one row at a time and checks the condition what you have issued (salary = 42000) and if it finds any row which satisfy s your condition then it calls the I/O device saves the record in the cache memory and continues to scan the other records till all the records from the table is scanned!! once this process is done the output device will display the output in the interface your using ....
  
  This is what happens when you execute a query (basically for select statement, for other statements it will differ and I am not explaining them now) these process happens so fast in matter of micro seconds you get the out put in a faster way but still if your going to query to a table which has millions of records obviously the time taken to execute the query is gonna increase as too much use of your scan, I/O device is required for example there is a table with 100 columns and 1000 records so the scan device has to scan all the 100 columns 1000 times which will take time.
  
  So when you use index on a particular column of a table then the database engine creates a table called the index table which has address location where the datas are stored (similar to hashing algorithm) so when you apply index on the the column named salary in the EMP table the database engine creates an index table which will have 2 columns like: DATA and LOCATION . So the data column will have the actual record from the salary column and location column will have the address location where the data is located in the database memory. So when you execute the same query again (which now has an index) it will run fast as the number of column which has to be scanned is reduced from 5 to 2 as the index table is the one which scanned to identify the location of the record.
  
  In more simple words in a book which has 1000 pages which of the following will be easy for you to search for a topic:
  
  -> turn each and every page to find the topic
  -> look at the index page of the book or glossary to find the topic and the page number
  
  obviously 2nd option right? so your index in databases works the same way and that's why they named this concept in database as INDEX .
  
  NOTE: It is a logical table which can be accessed only by the database engine so you will not be able to see what is inside the index table.
  there are 2 different types of index clustered and non clustered, If you want I will explain about them too PLEASE FEEL FREE TO ASK!!
  
  Hope this helped!! 😀
  
  -ARVIND
• 

  king99

  Hi thanks for your detail reply , thanks a lot but I have got some questions , I learnt types of indexes such as primary, secondary,cluster, B* tree searching etc but I understood only theoritical part out of it such as what is anchor or how block pointer works how we can use multilevel index over primary one but in initial stage I want to just use basic principle of index and query processing principle to understand how indexing will be helpful in each of case some specified it will not go much in depth such as types of index but instead determine how much time will be required using index on salary and type of query such as have we use elect * with index what will happen , or using joints and index, or using words like set with index. I am confused by two following questions and please help me to understand this
  
  1) This is simpler question say I can index on salary
  table is Emp(empno,ename,hiredate,job,salary)
  98% salary lie in 40000 to 45000
  
  So if I have following query would indexing help
  
  1) Select * from emp 2) select ename , job,salary/365 from emp
  where ename='BOB' where salary=4000
  
  Please tell me about above queries which will be more effective and consume less or more time if we index on salary field .My aim is not to just get answers but pls tell me link or website where I can learn in detail .
• 

  king99

  pls can any one help explaining above ??
• 

  slashfear

  Hi king,
  
  So you have placed an index on the column named salary right! so in you query when you filter the result sets from the table using where condition on the salary column alone the results will be faster because of the index placed.... so to your question now
  
  Q) select * from emp
  
  This one will the normal way with the index or without the index since your selecting all the records and columns from the table.
  
  Q) select ename,job, salary/365 from emp where ename ='BOB' salary = 4000
  
  The query is wrong in the first place it should be either,
  
  select ename,job, salary/365 from emp where ename ='BOB' and salary = 4000
  
  or
  
  select ename,job, salary/365 from emp where ename ='BOB' or salary = 4000
  
  
  Ok now according to the query you have provided, the first condition ename='BOB' is the one which is going to be checked and the resulting result set is going to be checked for the second condition salary= 4000 . So in this case your not performing a search directly on the indexed column so the second part will work fast but the first part will take normal time, so change the query like,
  
  
  select ename,job, salary/365 from emp where salary = 4000 and ename ='BOB'
  
  or
  
  select ename,job, salary/365 from emp where salary = 4000 or ename ='BOB'
  
  Now what happens is the first condition salary = 4000 is gonna be checked which has a index on it so it will faster to find that records through the index table and then apply the second condition on the result set you got from first condition which will be faster.
  
  
  Hope that helped 😉
  
  -Arvind
• 

  king99

  hi guys , index is placed on salary
  i am sorry i am just learner 😀 😀 so some mistake took place in it query is
  select * from emp other query is select ename,job,salary/365 from emp other query is select empno,salary from emp
  where ename='BOB'; where salary=4000 where salary/365>12;
  
  I am extremely sorry I made mistake in typing the query down (formatting ) but corrected here . My request to slashfear is to explain the above queries ,pls do give link of document from where I can understand this concept clearly if it is possible , thanks for replies !!