Ivan Jozsef Balazs wrote:It is however an interesting question whether the presence or absence of an index on the column deptId has an effect on the execution speed of the query.
It is, of course, database dependent, but I'd say it doesn't. Generally, the database has to visit all rows to answer the query, and therefore it won't use an index.
What might help in some databases (eg. Oracle) would be a composite index on the
deptId and
EmpSalary columns. If the table contained significantly more columns than these two, the index might take less space than the whole table, and since all the data needed to answer the query are contained in the index, the DB might read the whole index instead of the table and save some IO operations, thus possibly speeding things up.
Also, this statement is a bit problematic:
pramod mhatre wrote:If yes then please check whether index created on column from where clause. If its not then create it. It will reduce processing time more than 50%.
(Emphasis added)
Even in simple queries with just a
WHERE clause, how much an index will help depends on a lot of things, such as selectivity and the total number of rows in the table. I believe that in most databases it is possible to create a scenario in which adding an index makes even such a simple query perform
worse.
Indexes are not magical devices that will instantly speed everything up by X percent. Without understanding at least the very basics of how indexes and databases work (and knowing the structure and quantity of the data), it is not possible to say with any certainty whether an index will help or not. I know this very well - at the beginning of my career, I made exactly this mistake, throwing indexes randomly around and being surprised they didn't help.