The book SQL Antipatterns covers this topic in depth in the chapter "Implicit Columns".
The most typical use of a wildcard for the fieldnames in a query is when writing an ad-hoc query. It is faster to type '*' than it is to type in each field name that you want returned. Programmer time is fairly expensive, so it makes sense not to worry about the slight performance gain when running an ad-hoc query, where you'll waste more time typing in the names.
However, you still should have some knowledge of the database schema, because waiting for the values from a blob column on a large number of rows can waste a lot of time too, especially if all you wanted was the primary key value.
Using SELECT *
, you might be getting good performance on a particular query because it's returning a small result set of maybe a few hundred bytes, however, add a couple of blob fields to the table for some other function, and maybe now the result set is in the millions of bytes, and suddenly your query is slow, even though the data that you actually use is very small. To avoid this problem, name the columns in your query.
If you're using SELECT *
just so you automatically have the values of new columns when they're added, you'll still be victim to the above scenario. It's a trade off, which you will have to choose.
It's valid to use SELECT *
as long as you have a reason, with the exception that laziness is not a valid reason.