Is database index useful if query returns all rows ?

Question

If I used an Index column in a query which returns all the Rows, is it advantageous to use the Index column in the where clause ?

For eg. select * from table1 where Salary > 1;

If the salary of all the employees is greater than 1, is it advantageous to use the Index column in the where clause ?

Are Indexed a big overhead while inserting if the Database is most likely to be used as above ?

Answer 1

If your query returns all the rows, all the columns and has no ordering an index would not help this query at all. There would be no point putting bogus predicates in that always evaluate to true.

Answer 2

Indexes are useless when You perform full scans without ORDER BY . Oracle index organized table,Postgresl cluster table, MySQL (InnoDB) PRIMARY KEY clustered index: give big performance for ORDER BY.

http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html

"Are Indexed a big overhead while inserting if the Database is most likely to be used as above ?" If index fit into RAM everything is ok.

Answer 3

The form of the question seems a bit confusing. I don't think you are really asking whether it is advantageous to use a certain WHERE clause. Surely if you want to return rows where Salary > 1 then it's advantageous to specify that - otherwise you might not get back what you expect (the data might have changed)!

I assume what you really mean to ask is whether this query could perform better with a index than without it. I don't think we necessarily have enough information to answer that. We don't know what DBMS you are using or how the tables are stored or what type of index might be used. For example if the query is a query on a view and the view is indexed or the table(s) underlying it are indexed then indexes could make a big difference to the performance.

However, it's usually poor practice to specify SELECT * in a query. Specifying SELECT * means you will get all columns, even if the set of columns has changed. Better to specify only those columns you need because that way you stand a better chance of getting the most efficient execution plan.