mysql - Is it bad for performance to select all columns?

Question

Is it bad to SELECT all columns at once even though you probably don't neeed all of them? However you might need them in another task but you are to lazy to write queries for every task.

Should you only do queries where you SELECT only columns you need and do this query again if you need another column?

So basically the question is: Does it has any effect on performance to SELECT one column vs multiple columns?

The query is very simple (no functions, joins etc.) For example:

SELECT
id, name, status, date
FROM user_table
WHERE user_id = :user_id

Answer 1

The issue here isn't so much a matter of the database server, as just the network communication. By selecting all columns at once, you're telling the server to return to you, all columns at once. As for concerns over IO and all that, those are addressed nicely in the question and answer @Karamba gave in a comment: select * vs select column. But for most real-world applications (and I use "applications" in every sense), the main concern is just network traffic and how long it takes to serialize, transmit, then deserialize the data. Although really, the answer is the same either way.

So pulling back all the columns is great, if you intend to use them all, but that can be a lot of extra data transfer, particularly if you store, say, lengthy strings in your columns. In many cases, of course, the difference will be undetectable and is mostly just a matter of principle. Not all, but a significant majority.

It's really just a trade-off between your aforementioned laziness (and trust me, we all feel that way) now and how important performance really is.

That all said, if you do intend to use all the column values, you're much better off pulling them all back at once then you are filing a bunch of queries.

Think of it like doing a web search: you do your search, you find your page, and you only need one detail. You could read the entire page and know everything about the subject, or you could just jump to the part about what you're looking for and be done. The latter is a lot faster if that's all you ever want, but if you're then going to have to learn about the other aspects, you'd be way better off reading them the first time than having to do your search again and find the site to talk about it.

If you aren't sure whether you'll need the other column values in the future, then that's your call to make as the developer for which case is more likely.

It all depends on what your application is, what your data is, how you're using it, and how important performance really is to you.