Why is SELECT * considered harmful?

Question

Why is SELECT * bad practice? Wouldn't it mean less code to change if you added a new column you wanted?

I understand that SELECT COUNT(*) is a performance problem on some DBs, but what if you really wanted every column?

Survival

SELECT COUNT(*) being bad is incredibly old and outdated. For info on SELECT * - see: #1960536 – Leading

SELECT COUNT(*) gives a different answer from SELECT COUNT(SomeColumn) unless the column is a NOT NULL column. And the optimizer can give SELECT COUNT(*) special treatment - and usually does. Also note that WHERE EXISTS(SELECT * FROM SomeTable WHERE ...) is given special case treatment. – Cult

possible duplicate of Can select * usage ever be justified? – Calabash

@Michael Mrozek, actually it's the inverse of the question. I am asking if it as ever harmful, not if it was ever not harmful. – Survival

On which relational/sql databases SELECT COUNT(*) is NOT a performance problem? – Goal

All of them, if you properly use it. – Emlin

@Bytecode Ninja: specifically, MySQL with MyISAM engine has an optimisation for COUNT(*) : mysqlperformanceblog.com/2007/04/10/count-vs-countcol – Quay

@Dave: I assume the OP's question is referring to a select count() statement without a where clause ("I understand that SELECT COUNT() is a performance problem on some DBs"), and on most DBs select count(*) requires a whole table scan. The question is on which DBs it doesn't need a whole table scan? Apparently MySQL/MyISAM is one of them... – Goal

I wasn't assuming there was no WHERE clause. That's the big difference. Also, at least on SQL Server, you'll likely find that a simple SELECT COUNT(*) will scan only the smallest index of the table to fetch the row count, not necessarily the clustered index or underlying heap which represents the table. – Emlin

For SQL Server see sqlblog.org/blogs/aaron_bertrand/archive/2009/10/10/… – Cleareyed

Also related (possibly duplicate): stackoverflow.com/questions/321299 – Cyclometer

SELECT COUNT(*) is the standard notation for counting all the rows. It does not touch every column. It is unrelated to SELECT *. In InnoDB, it is performed using the narrowest index. – Meadow

Answer 1

There are really three major reasons:

• Inefficiency in moving data to the consumer. When you SELECT *, you're often retrieving more columns from the database than your application really needs to function. This causes more data to move from the database server to the client, slowing access and increasing load on your machines, as well as taking more time to travel across the network. This is especially true when someone adds new columns to underlying tables that didn't exist and weren't needed when the original consumers coded their data access.

• Indexing issues. Consider a scenario where you want to tune a query to a high level of performance. If you were to use *, and it returned more columns than you actually needed, the server would often have to perform more expensive methods to retrieve your data than it otherwise might. For example, you wouldn't be able to create an index which simply covered the columns in your SELECT list, and even if you did (including all columns [shudder]), the next guy who came around and added a column to the underlying table would cause the optimizer to ignore your optimized covering index, and you'd likely find that the performance of your query would drop substantially for no readily apparent reason.

• Binding Problems. When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names.

But it's not all bad for SELECT *. I use it liberally for these use cases:

• Ad-hoc queries. When trying to debug something, especially off a narrow table I might not be familiar with, SELECT * is often my best friend. It helps me just see what's going on without having to do a boatload of research as to what the underlying column names are. This gets to be a bigger "plus" the longer the column names get.

• When * means "a row". In the following use cases, SELECT * is just fine, and rumors that it's a performance killer are just urban legends which may have had some validity many years ago, but don't now:

  SELECT COUNT(*) FROM table;
  

  in this case, * means "count the rows". If you were to use a column name instead of * , it would count the rows where that column's value was not null. COUNT(*), to me, really drives home the concept that you're counting rows, and you avoid strange edge-cases caused by NULLs being eliminated from your aggregates.

  Same goes with this type of query:

  SELECT a.ID FROM TableA a
  WHERE EXISTS (
      SELECT *
      FROM TableB b
      WHERE b.ID = a.B_ID);
  

  in any database worth its salt, * just means "a row". It doesn't matter what you put in the subquery. Some people use b's ID in the SELECT list, or they'll use the number 1, but IMO those conventions are pretty much nonsensical. What you mean is "count the row", and that's what * signifies. Most query optimizers out there are smart enough to know this. (Though to be honest, I only know this to be true with SQL Server and Oracle.)

Emlin

Using "SELECT id,name" is as likely as "SELECT *" to select two columns of the same name from two different tables when using joins. Prefixing with table name solves the problem in both cases. – Kroll

I know this is older, but it's what was pulled up while googling so I'm asking. "When * means "a row". In the following use cases, SELECT * is just fine, and rumors that it's a performance killer are just urban legends..." do you have any references here? Is this statement due to hardware being more powerful (if that's the case it doesn't mean that it's not inefficient just that you are less likely to notice it). I'm not trying to second guess per se I'm just wondering where this statement comes from. – Gesture

As far as references go, you can examine the query plans -- they're identical in cases when you have a "*" in the subquery versus when you select a column. They are identical because the cost-based optimizer "recognizes" that semantically, you're talking about any row which satisfies the criteria -- it's not a question of hardware or speed. – Emlin

One more advantage of using * is that in some situations it can take better advantage of MySQL's cache systems. If you're running large numbers of similar select queries that request different column names (select A where X,select B where X,...) using a select * where X will allow the cache to handle a larger number of the queries which can result in a substantial performance boost. It's an application-specific scenario, but it's worth keeping in mind. – Marchak

re: Inefficiency in moving data to ...:agree but only in situation when you need only some of the data. It dosn't apply situation when you know that you need all data and will always need all data (even when new colums are added). re: Indexing issues - as above,Re: Binding Problems - most of it would be dealt with sql compiler that would just say that columns are ambiguous.Stating that something is ALWAYS wrong is inmature therefore question 'why select * is bad' isn't OK. Such Q. should be always considered in context and ALL should be replaced with when you do such and such stuff – Outstation

@rgasiore: As to your first point, maybe you're right for a small application where you are the only developer on the team, and there's no longevity to it -- a throw-away app. But if you're working on a team, or your database is in any way subject to change, using SELECT * is the wrong move. Just because you're consuming all of your columns today doesn't mean that you will be doing so tomorrow. As far as your second point goes, you may want to read my example for SQL Server, as well as re-read my post and do some thinking. – Emlin

8+ years later, but want to add a point about ambiguity that was not mentioned. Working with 200+ tables in a database and having a mixture of naming conventions. When reviewing code that interacts with query results, SELECT * forces developers to look at the table schema(s) involved, to determine the columns affected/available, such as within a foreach or serialize. The task of repeatedly looking at schemas to track down what is happening, will inevitably increase total time involved both in debugging and developing related code. – Maiolica

Also 8+ years later - When selecting from a derived table or a cte, select * is not that bad, if you have already selected only the columns you need in the derived table / cte in advance. – Adenocarcinoma

11+ years later ... the first two reasons involve [more columns than you need] and so are moot if you need all of the columns, and inconsequential for tables with few columns and or few rows. The third reason, binding issues, I've not run into that in production, such issues are identified during development. Granted that a developer might add a duplicated name to a table and ruin existing queries, but what do you do when joining two tables of a 100+ columns each and you need all of the columns? List all 200+ columns?? – Scrivens

Answer 2

The asterisk character, "*", in the SELECT statement is shorthand for all the columns in the table(s) involved in the query.

Performance

The * shorthand can be slower because:

• Not all the fields are indexed, forcing a full table scan - less efficient
• What you save to send SELECT * over the wire risks a full table scan
• Returning more data than is needed
• Returning trailing columns using variable length data type can result in search overhead

Maintenance

When using SELECT *:

• Someone unfamiliar with the codebase would be forced to consult documentation to know what columns are being returned before being able to make competent changes. Making code more readable, minimizing the ambiguity and work necessary for people unfamiliar with the code saves more time and effort in the long run.
• If code depends on column order, SELECT * will hide an error waiting to happen if a table had its column order changed.
• Even if you need every column at the time the query is written, that might not be the case in the future
• the usage complicates profiling

Design

SELECT * is an anti-pattern:

• The purpose of the query is less obvious; the columns used by the application is opaque
• It breaks the modularity rule about using strict typing whenever possible. Explicit is almost universally better.

When Should "SELECT *" Be Used?

It's acceptable to use SELECT * when there's the explicit need for every column in the table(s) involved, as opposed to every column that existed when the query was written. The database will internally expand the * into the complete list of columns - there's no performance difference.

Otherwise, explicitly list every column that is to be used in the query - preferably while using a table alias.

Leading

Design: The purpose of a query is not likely to be evident if several hundred columns are return (medical field, e.g.). The name of the DAO method running the query is a better place to provide the purpose. And "who has not ever" used an existing query because it has everything (and more) than is needed and is already written? – Scrivens

"Someone unfamiliar with the codebase would be forced to consult documentation to know what columns are being returned before being able to make competent changes." That sounds like a great reason to use "select *". – Scrivens

Answer 3

Even if you wanted to select every column now, you might not want to select every column after someone adds one or more new columns. If you write the query with SELECT * you are taking the risk that at some point someone might add a column of text which makes your query run more slowly even though you don't actually need that column.

Wouldn't it mean less code to change if you added a new column you wanted?

The chances are that if you actually want to use the new column then you will have to make quite a lot other changes to your code anyway. You're only saving , new_column - just a few characters of typing.

Tramroad

Especially if that new column is a three-megabyte BLOB – Ruling

@Matti - But hopefully they would put in more thought than "Hey lets plop a huge BLOB column onto this table!". (Yes a fools hope I know but can't a guy dream?) – Trafficator

Performance is one aspect, but often there is also a correctness aspect: the shape of the result projected with * can unexpectedly change and this can wreak havoc in the application itself: columns referenced by ordinal (eg. sqldatareader.getstring(2)) suddenly retrieve a different column, any INSERT ... SELECT * will break and so on and so forth. – Incoordination

@chaos: putting blobs on tables isn't really going to hurt your performance much... Unless you use SELECT *... ;-) – Emlin

@Dave - What I was trying to imply was that typically developers will just say "Let's just add another column to the table." instead of thinking about how it might disturb the integrity of their database. – Trafficator

@Chaos True, true. And it's even worse when developers say, "let's just add another index to the table..." – Emlin

You shouldn't worry about performance until it causes real issues. And also, SELECT * is not a matter of saving few characters. It's a matter of saving hours of debugging time because it's easy to forget to specify new added columns. – Byword

I would hate to be the developer who had listed several hundred columns on a select and then had to debug why "getString(248)" was returning the wrong column when "getString("the_right_column")" could have been used. – Scrivens

Answer 4

If you really want every column, I haven't seen a performance difference between select (*) and naming the columns. The driver to name the columns might be simply to be explicit about what columns you expect to see in your code.

Often though, you don't want every column and the select(*) can result in unnecessary work for the database server and unnecessary information having to be passed over the network. It's unlikely to cause a noticeable problem unless the system is heavily utilised or the network connectivity is slow.

Nicolella

Answer 5

If you name the columns in a SELECT statement, they will be returned in the order specified, and may thus safely be referenced by numerical index. If you use "SELECT *", you may end up receiving the columns in arbitrary sequence, and thus can only safely use the columns by name. Unless you know in advance what you'll be wanting to do with any new column that gets added to the database, the most probable correct action is to ignore it. If you're going to be ignoring any new columns that get added to the database, there is no benefit whatsoever to retrieving them.

Chafin

"may thus safely be referenced by numerical index" but who would be stupid enough to ever try and reference a column by numerical index instead of it's name!? That's a much worse anti-pattern than using select * in a view. – Virchow

@MGOwen: Using select * and then using the columns by index would be horrible, but using select X, Y, Z or select A,B,C and then passing the resulting data reader to code which expects to do something with the data in columns 0, 1, and 2 would seem a perfectly reasonable way to allow the same code to act upon either X,Y,Z or A,B,C. Note that the indices of columns would depend upon their location within the SELECT statement, rather than their order in the database. – Chafin

Suppose you are requesting 500 of the 900+ columns from an EMR table - how much work is done by the database engine putting those columns in the requested order? How much work is done by the programmer entering those 500 column names and spelling them correctly? How much work is done getting the column numbers correct? There certainly are tradeoffs. – Scrivens

Answer 6

In a lot of situations, SELECT * will cause errors at run time in your application, rather than at design time. It hides the knowledge of column changes, or bad references in your applications.

Greeson

So how does naming the columns help? In SQL Server, existing queries, embedded in code or SPs, won't complain until they run, even if you've named the columns. New ones will fail when you test them, but plenty of time you have to go looking for SPs affected by table changes. What sort of situations are you referring to that would be caught at design time? – Protomartyr

Answer 7

Think of it as reducing the coupling between the app and the database.

To summarize the 'code smell' aspect:
SELECT * creates a dynamic dependency between the app and the schema. Restricting its use is one way of making the dependency more defined, otherwise a change to the database has a greater likelihood of crashing your application.

Veronicaveronika

Answer 8

I don't think that there can really be a blanket rule for this. In many cases, I have avoided SELECT *, but I have also worked with data frameworks where SELECT * was very beneficial.

As with all things, there are benefits and costs. I think that part of the benefit vs. cost equation is just how much control you have over the datastructures. In cases where the SELECT * worked well, the data structures were tightly controlled (it was retail software), so there wasn't much risk that someone was going to sneek a huge BLOB field into a table.

Jenisejenkel

Answer 9

If you add fields to the table, they will automatically be included in all your queries where you use select *. This may seem convenient, but it will make your application slower as you are fetching more data than you need, and it will actually crash your application at some point.

There is a limit for how much data you can fetch in each row of a result. If you add fields to your tables so that a result ends up being over that limit, you get an error message when you try to run the query.

This is the kind of errors that are hard to find. You make a change in one place, and it blows up in some other place that doesn't actually use the new data at all. It may even be a less frequently used query so that it takes a while before someone uses it, which makes it even harder to connect the error to the change.

If you specify which fields you want in the result, you are safe from this kind of overhead overflow.

Favor

It is actually not true that new fields will be included in the query in the case of table valued functions – Harmonica

This: "it will actually crash your application at some point" is an unfounded conclusion. "There is a limit for how much data you can fetch in each row of a result" - and what happens if you do need all of the columns? – Scrivens

@user3481644: No, it's not unfounded. If you keep adding fields to a table, at some point you can no longer fetch all fields in a query. That is a simple fact. If you need all of the columns but they can't be fetched in one query, then you just need to use more queries. – Favor

Answer 10

Reference taken from this article.

Never go with "SELECT *",

I have found only one reason to use "SELECT *"

If you have special requirements and created dynamic environment when add or delete column automatically handle by application code. In this special case you don’t require to change application and database code and this will automatically affect on production environment. In this case you can use “SELECT *”.

Okwu

Answer 11

Generally you have to fit the results of your SELECT * ... into data structures of various types. Without specifying which order the results are arriving in, it can be tricky to line everything up properly (and more obscure fields are much easier to miss).

This way you can add fields to your tables (even in the middle of them) for various reasons without breaking sql access code all over the application.

Homothermal

Answer 12

Using SELECT * when you only need a couple of columns means a lot more data transferred than you need. This adds processing on the database, and increase latency on getting the data to the client. Add on to this that it will use more memory when loaded, in some cases significantly more, such as large BLOB files, it's mostly about efficiency.

In addition to this, however, it's easier to see when looking at the query what columns are being loaded, without having to look up what's in the table.

Yes, if you do add an extra column, it would be faster, but in most cases, you'd want/need to change your code using the query to accept the new columns anyways, and there's the potential that getting ones you don't want/expect can cause issues. For example, if you grab all the columns, then rely on the order in a loop to assign variables, then adding one in, or if the column orders change (seen it happen when restoring from a backup) it can throw everything off.

This is also the same sort of reasoning why if you're doing an INSERT you should always specify the columns.

Infrastructure

Answer 13

Selecting with column name raises the probability that database engine can access the data from indexes rather than querying the table data.

SELECT * exposes your system to unexpected performance and functionality changes in the case when your database schema changes because you are going to get any new columns added to the table, even though, your code is not prepared to use or present that new data.

Stickweed

Answer 14

There is also more pragmatic reason: money. When you use cloud database and you have to pay for data processed there is no explanation to read data that you will immediately discard.

For example: BigQuery:

Query pricing

Query pricing refers to the cost of running your SQL commands and user-defined functions. BigQuery charges for queries by using one metric: the number of bytes processed.

and Control projection - Avoid SELECT *:

Best practice: Control projection - Query only the columns that you need.

Projection refers to the number of columns that are read by your query. Projecting excess columns incurs additional (wasted) I/O and materialization (writing results).

Using SELECT * is the most expensive way to query data. When you use SELECT *, BigQuery does a full scan of every column in the table.

Bemuse

Answer 15

Understand your requirements prior to designing the schema (if possible).

Learn about the data, 1)indexing 2)type of storage used, 3)vendor engine or features; ie...caching, in-memory capabilities 4)datatypes 5)size of table 6)frequency of query 7)related workloads if the resource is shared 8)Test

A) Requirements will vary. If the hardware can not support the expected workload, you should re-evaluate how to provide the requirements in the workload. Regarding the addition column to the table. If the database supports views, you can create an indexed(?) view of the specific data with the specific named columns (vs. select '*'). Periodically review your data and schema to ensure you never run into the "Garbage-in" -> "Garbage-out" syndrome.

Assuming there is no other solution; you can take the following into account. There are always multiple solutions to a problem.

1) Indexing: The select * will execute a tablescan. Depending on various factors, this may involve a disk seek and/or contention with other queries. If the table is multi-purpose, ensure all queries are performant and execute below you're target times. If there is a large amount of data, and your network or other resource isn't tuned; you need to take this into account. The database is a shared environment.

2) type of storage. Ie: if you're using SSD's, disk, or memory. I/O times and the load on the system/cpu will vary.

3) Can the DBA tune the database/tables for higher performance? Assumming for whatever reason, the teams have decided the select '*' is the best solution to the problem; can the DB or table be loaded into memory. (Or other method...maybe the response was designed to respond with a 2-3 second delay? --- while an advertisement plays to earn the company revenue...)

4) Start at the baseline. Understand your data types, and how results will be presented. Smaller datatypes, number of fields reduces the amount of data returned in the result set. This leaves resources available for other system needs. The system resources are usually have a limit; 'always' work below these limits to ensure stability, and predictable behaviour.

5) size of table/data. select '*' is common with tiny tables. They typically fit in memory, and response times are quick. Again....review your requirements. Plan for feature creep; always plan for the current and possible future needs.

6) Frequency of query / queries. Be aware of other workloads on the system. If this query fires off every second, and the table is tiny. The result set can be designed to stay in cache/memory. However, if the query is a frequent batch process with Gigabytes/Terabytes of data...you may be better off to dedicate additional resources to ensure other workloads aren't affected.

7) Related workloads. Understand how the resources are used. Is the network/system/database/table/application dedicated, or shared? Who are the stakeholders? Is this for production, development, or QA? Is this a temporary "quick fix". Have you tested the scenario? You'll be surprised how many problems can exist on current hardware today. (Yes, performance is fast...but the design/performance is still degraded.) Does the system need to performance 10K queries per second vs. 5-10 queries per second. Is the database server dedicated, or do other applications, monitoring execute on the shared resource. Some applications/languages; O/S's will consume 100% of the memory causing various symptoms/problems.

8) Test: Test out your theories, and understand as much as you can about. Your select '*' issue may be a big deal, or it may be something you don't even need to worry about.

Monster

Answer 16

There's an important distinction here that I think most answers are missing.

SELECT * isn't an issue. Returning the results of SELECT * is the issue.

An OK example, in my opinion:

WITH data_from_several_tables AS (
    SELECT * FROM table1_2020
        UNION ALL
    SELECT * FROM table1_2021
    ...
)
SELECT id, name, ...
FROM data_from_several_tables
WHERE ...
GROUP BY ...
...

This avoids all the "problems" of using SELECT * mentioned in most answers:

• Reading more data than expected? Optimisers in modern databases will be aware that you don't actually need all columns
• Column ordering of the source tables affects output? We still select and return data explicitly.
• Consumers can't see what columns they receive from the SQL? The columns you're acting on are explicit in code.
• Indexes may not be used? Again, modern optimisers should handle this the same as if we didn't SELECT *

There's a readability/refactorability win here - no need to duplicate long lists of columns or other common query clauses such as filters. I'd be surprised if there are any differences in the query plan when using SELECT * like this compared with SELECT <columns> (in the vast majority of cases - obviously always profile running code if it's critical).

Dulcinea