MySql queries: really never use SELECT * ?

Question

I'm a self taught developer and ive always been told not to use SELECT *, but most of my queries require to know all the values of a certain row...

what should i use then? should i list ALL of the properties every time? like Select elem1,elem2,elem3,....,elem15 FROM...?

thanks

Answer 1

List only the columns that you need, ideally with a table alias:

SELECT t.elem1,
       t.elem2
  FROM YOUR_TABLE t

The presence of a table alias helps demonstrate what is a column (and where it's from) vs a derived column.

Answer 2

If you really need all the columns and you're fetching the results by name, I would go ahead and use SELECT *. If you're fetching the row results by index, then it makes sense to specify the column names or else they might not be in the order you expect (especially if the table schema changes).

Answer 3

The only real performance hit you take from using select * is in the bandwidth required to send back extra columns in your result set, if they're not necessary. Other than that, there's nothing inherently "bad" about using select *.

Answer 4

If you absolutely have to use *, try to limit it to a specific table; e.g.:

SELECT t.*
  FROM mytable t

Answer 5

SELECT * FROM ... is not always the best way to go unless you need all columns. That's because if for example a table has 10 columns and you only need 2-3 of them and these columns are indexed, then if you use SELECT * the query will run slower, because the server must fetch all rows from the datafiles. If instead you used only the 2-3 columns that you actually needed, then the server could run the query much faster if the rows were fetch from a covering index. A covering index is one that is used to return results without reading the datafile.

So, use SELECT * only when you actually need all columns.

Answer 6

If you are positive that you will always need all the columns then select * should be ok. But my reasoning for avoiding it is: say another developer has another column added to the table which isn't required by your query..then there is overhead. This can get worse as more columns get added.

Answer 7

You might SELECT * from a subquery.

Answer 8

Yes, Select * is bad. You do not state what language you will be using process the returned data. Suppose you receive these records back as an array (not a hash map). In this case, what is in Row[12]? Maybe it was ZipCode when you wrote the app, but guess what happens when someone inserts a field SuiteNumber before ZipCode.

Or suppose the next coder appends a huge blob field to each record. Ouch!

Or a little more subtle: let's assume you're doing a join or subselect, and you have no Text or Blob type fields. MySQL will create any temp files it needs in memory. But as soon as you include a Text field (even TinyText), MySQL will need to create the temp file on disk, and call sort-merge. This won't break the program, but it can kill performance.

Select * is sacrificing maintainability in order to save a little typing.