using asterisk in select queries

Question

I hear alot not to use a * (asterisk) but name the fields I need. Is it because of performance issues or security reasons? can someone tell some good arguments for this?

So not

SELECT * FROM users WHERE name='John';

but instead

SELECT name FROM users WHERE name='John';

This is one of many superstitions connected to poor language of PHP. Most of time it just doesn't matter. — Your Common Sense, Mar 06 '13 at 15:08
Have requested this to be re-opened as the question this is a duplicate of does not have any good aswers. Indeed, the accepted answer is wrong. — symcbean, Mar 06 '13 at 15:16
@symcbean Then scroll down to the [***best*** answer](http://stackoverflow.com/a/3180435/679449). — Kermit, Mar 06 '13 at 15:20
@symcbean Indeed. Please read ***ALL*** of the answers, not just the accepted answer. The asker doesn't always know best, and the accepted answer is not always the best answer. — Aaron Bertrand, Mar 06 '13 at 15:56
Also see http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful — Aaron Bertrand, Mar 06 '13 at 16:14

score 8 · Answer 1 · answered Mar 06 '13 at 15:06

The biggest reason not to use asterisks in production queries is insulating your non-SQL code from changes in the database schema, or making it harder for such changes to go unnoticed.

For example, if your code queries with an asterisk expecting a name in the first column and an address in the second, if the schema changes to add a third column in front of the two that you are already reading, your code would get that new column instead of names, and also put names in place of addresses. The worst thing is that it would do all that without a crash: the users would simply see garbage data.

In contrast, a query with explicitly named columns would either get the right columns despite the schema change, or break if a required column has been removed or renamed. This would point to the source of the problem right away, making it easy for you to investigate and fix the problem.

score 2 · Answer 2 · answered Mar 06 '13 at 15:06

It's a performance thing. When you SELECT *, you are retrieving every field in the table. If you SELECT name, you are only selecting the name field. There can be a lot less data to transfer from the DB server to the application when selecting only the fields you need.

score 0 · Answer 3 · answered Mar 06 '13 at 15:05

0

SELECT * means SELECT ALL from your table .It wills elect all columns.

but if you specify to select just some columns like in your query

SELECT name ---> this will select only name column

answered Mar 06 '13 at 15:05

echo_Me

37,078
5
58
78

score 0 · Answer 4 · answered Mar 06 '13 at 15:07

0

because if you select *, then you are getting all columns for that query, which translates to higher memory usage for your dataset, rather than just getting the columns that you need.

answered Mar 06 '13 at 15:07

TravellingGeek

1,581
11
16

Your Common Sense · Answer 5 · 2013-03-06T15:52:45.310

-7

To me it's just more like question of sanity, readability and common sense.

When you need only name - why not to write it explicitly?
It will make your query a lot more informative to the reader.

But when I see poor developers writing dozens of field names explicitly only to follow this "performance" crap - it makes me hurt.

An important note from the comments:
It's indeed important to write the code that doesn't rely on the order or number of fields in the query.
So, just use *, fetch associative array and your code will never break for such a silly reason.

For my humble tests I've got some difference. 0,0002 fraction of second.
Well, if such a difference is a certain bottleneck for your application - go for explicit writing fields (although it will take a parser more time to parse your query, hehe :)
However, in my realm performance concerns starts from at least 0,001s difference. So, I wouldn't bother.

edited Mar 06 '13 at 15:52

answered Mar 06 '13 at 15:09

Your Common Sense

156,878
40
214
345

3

Well, no offense, but I think that those "hurtful" times are better than having code that breaks when there is a change on the tables – Lamak Mar 06 '13 at 15:27
2

Sorry that your eyes melt from reading code. This is not an answer. – Kermit Mar 06 '13 at 15:32
1

Couldn't agree more with @Lamak Select * is meant for investigation and things of that nature. – Zane Mar 06 '13 at 15:32
3

The problem is that you seem to believe that performance is the only issue with `SELECT *`, when there are so many other more important ones with it – Lamak Mar 06 '13 at 15:57
have had to optimize plenty of queries in big data ... depends on your use case i suppose – ladieu Jul 06 '22 at 23:55

using asterisk in select queries

5 Answers5