Why shouldn't we use Select * in a mysql query on a production server?

Question

Based on this question here Selecting NOT NULL columns from a table One of the posters said

you shouldn't use SELECT * in production.

My Question: Is it true that we shouldn't use Select * in a mysql query on a production server? If yes, why shouldn't we use select all?

Answer 1

Most people do advise against using SELECT * in production, because it tends to break things. There are a few exceptions though.

• SELECT * fetches all columns - while most of the times you don't need them all. This causes the SQL-server to send more columns than needed, which is a waste and makes the system slower.
• With SELECT *, when you later add a column, the old query will also select this new column, while typically it will not need it. Naming the columns explicitly prevents this.
• Most people that write SELECT * queries also tend to grab the rows and use column order to get the columns - which WILL break your code once columns are injected between existing columns.
• Explicitly naming the columns also guarantees they are always in the same order, while SELECT * might behave differently when the table column order is modified.

But there are exceptions, for example statements like these:

INSERT INTO table_history
SELECT * FROM table 

A query like that takes rows from table, and inserts them into table_history. If you want this query to keep working when new rows are added to table AND to table_history, SELECT * is the way to go.

Answer 2

Remember that your database server isn't necessarily on the same machine as the program querying the database. The database server could be on a network with limited bandwidth; it could even be halfway across the world.

• If you really do need every column, then by all means do SELECT * FROM table.
• If you only need certain columns, though, it would waste bandwidth to ask for all columns using SELECT * FROM table only to throw half the columns away.

Other potential reasons it might be good to specify which exact columns you want:

• The database structure may change. If your program assumes certain column names, then it may fail if the column names change, for example. Explicitly naming the columns you want to retrieve will make the program fail immediately if your assumptions about the column names are violated.

• As @Konerak mentioned, naming the columns you want also ensures that the order of the columns in your result is the same, even if the table schema changes (i.e. inserting one column in-between two others.) This is important if you're depending on FirstName being the [2]nd element of a result.

  (Note: a more robust and self-documenting way of dealing with this is to ask for your database results as a list of key-value pairs, like a PHP associative array, Perl hash or a Python dict. That way you never need to use a number to index into the result (name = result[2] ) - instead you can use the column name: name = result["FirstName"].)