CODEWITHSUNDEEP
mysqlsql
mickburkejnr
mickburkejnr

Reputation: 3690

Does selecting specific values from an SQL statement improve efficiency compared to doing a "SELECT *"

This question has just came in to my mind, and I'm not in a position to answer it.

Let's say you are developing a news page for a website. In your table you would have the following fields:

ID, Title, Subject, Content, Time, Author, Keywords

On the actual page that the user will see, you may only want to display the following:

Title, Content, Keywords

Now, when you go to write the SQL statement, would it be better to write it as this:

SELECT Title, Content, Keywords FROM newstable;

Or as this:

SELECT * FROM newstable;

I always thought that using the former example would be far more efficient and quicker to use. However, I'm now thinking it might be quicker and easier to just use the select all statement.

Is either way right, or does it really matter how they are written?

Upvotes: 5

Views: 151

Answers (4)

Larry Lustig
Larry Lustig

Reputation: 50970

In addition to the performance reasons (smaller result set returned and the possibility of index-only retrieval), returning a known number of named columns in a specified order to your application code makes that code more robust — there's no chance you will accidentally code any dependency on the current definition of the table.

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

Yes, it matters for performance and other reasons.

  • When you select only the columns you need, less data must be returned. This means less time pulling it off the hard drive and sending it over the wire.
  • It is possible that the columns you select may be completely satisfied using a covering index, which can make the query many times faster
  • By specifying the column names, you are documenting what data you are using in your code. This is very helpful when you come back to do maintenance.
  • By not using *, you do not run the chance of your code breaking when columns are renamed, added or removed. If you are explicit with column names, you will get a query execution-time error, rather than the new result being blindly passed along to your code, where who-knows-what will happen.

Upvotes: 13

Randy
Randy

Reputation: 16677

i suggest best practice is to explicitly write out the columns.

first of all, this will minimize the amount of data being returned to only what you ask for.

next, it removes possible regression bugs if someone adds a column to the database.

also, it does not take much time.

finally, you will almost certainly have an order by clause, this could be satisfied by data from the index only.

Upvotes: 4

Taryn
Taryn

Reputation: 247620

Yes it does matter for performance. Plus the readability is better, then you know exactly what data you are requesting.

You should specific the columns of data that you need, if you don't need them all then why pull them. You would just be returning data that is unnecessary

Upvotes: 1

Related Questions