Easy SQL query performance question

When designing an SQL query, which is faster, selecting the whole row (select *) or only the fields you need? What if the results were fed to another q开发者_Go百科uery (eg a natural join)?

Selecting only the fields you need will generally be faster - especially if those fields are "covered" by an index, so that the database need only load the data from the index and not from the main table (thus allowing it to fetch far fewer pages).

Selecting only what you need is faster, that goes for columns and where clauses.

SELECT * is particularly dangerous when you add columns in the future. Lets say you have a routine that needs the UserID and LastLogon date. Last you add a UserNotes TEXT column to the table and Bang! that little routine will now be pulling some (potentailly vaste) text data too, performance of the whole application will deteriorate and EVERY Select statement in the whole application will need to be fixed to cure the problem!

Just select the columns the application needs :)

Selecting the fields you need is faster, because it's bring less data. this is a general advice to increase the query performance speed.

Selecting all columns can be faster if you access the table several times and the overhead of having more hard parses is bigger than the overhead of selecting more columns than necessary.

E.g. lets say you have a table foobar with columns a..c, all of them CHAR(1); then the following sequence of statements

SELECT a,b,c FROM foobar;
SELECT a,b FROM foobar;
SELECT a,c FROM foobar;
SELECT a FROM foobar;
SELECT b,c FROM foobar;
SELECT b FROM foobar;
SELECT c FROM foobar;

might be slower than executing

SELECT * FROM foobar;

seven times.