Sory , I cant find msmits alternative , so I am not changing my vote right now. I am aware select * is a memory hog , but I think it is acceptable to use it if you specify the full key.
I agree with Klaus. In the spirit of not throwing my own kind under the bus, is it possible this "consultant" was speaking of Indexes in a sql Select when there are many joins (particularly the dreaded left-join)?
This is nonsense - an index is only used based on the WHERE-clause, maybe the ORDER BY / GROUP BY clause, but NEVER EVER (except if all columns to deliver in the index) on the content that is to be selected, but it would be really unusual to have an index that spans all table columns !
That is not true.
The optimizer takes into account the cost of using the index and then finding the record (if the index is not covering). This estimation greatly depends on the distribution of the data (statistics).
Just because you have an index with all fields in the WHERE clause does not mean it will be used. In fact, using an index and then retrieving the other columns may degrade performance.
This is interesting. Did the consultant have any proof for the statement? Index is typically used as an access path to a row, no matter what you select from the row (since the whole block is fetched). The only special case that comes in mind is a covered query where there's no need for row access, but that's typically a rare case.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
