-3

Is it okay to always use SELECT * even if you only need one column when retrieving data from MySQL? Does it affect the speed of the query or the speed of the system? Thanks.

user3690458
  • 3
  • 2
  • Why don't you benchmark it and see? However, I see it as just being lazy. Select only the data you need. – Jonathon Reinhart Jun 23 '14 at 03:57
  • @JonathonReinhart would it be possible to recommend some benchmarking tools so this thread is more informative? I am curious what you would use. – La-comadreja Jun 23 '14 at 03:58
  • @La-comadreja Just code up some sort of example, call it a large number (10k, 1M, etc) of times, and time it with the "get current time" library call of whatever programming language you're using. – Jonathon Reinhart Jun 23 '14 at 04:04
  • 1
    This thread might help, http://stackoverflow.com/questions/362223/best-mysql-performance-tuning-tool – Suresh Jun 23 '14 at 04:10
  • 2
    `SELECT *` is the equivalent of emptying out your sock drawer every morning to choose a pair. `Select ` is the equivalent of picking just the pair you want to wear. You decide what is more efficient. – Raj Jun 23 '14 at 04:14
  • @Raj. Bad analogy. `SELECT *` is the equivalent of `SELECT left, right` here. not too bad. Your example is more about a WHERE clause. – Thilo Jun 23 '14 at 05:36

3 Answers3

1

No, it is not always okay.

But it is also not always a problem.

In order of performance impact:

If you only select a subset of columns, it can positively affect the access path. Maybe those columns can be read from an index without touching the table at all.

Beyond that, there is also raw network I/O. Sending three columns uses a lot less bandwidth than sending three hundred (especially for many rows).

Beyond that, there is also the memory required by your client application to process the result set.

Thilo
  • 257,207
  • 101
  • 511
  • 656
0

I believe the columns in the select are the least time/CPU intensive piece of the query. Limiting the number of rows, either by "WHERE" clauses or explicitly using "LIMIT" is where time is saved.

Brian Mego
  • 1,439
  • 11
  • 10
0

In my personal experience you should prefer named columns over SELECT * whenever possible.

But, performance is not the key reason.

  • Code that uses SELECT * is usually harder to read and debug as it is not explicit what the intent of the query is.

  • Code that uses SELECT * can break when the database structure is changed (referring to columns by index rather than by name is almost always the wrong way to write your code).

  • Finally, retrieving bigger datasets does affect speed, bandwidth and memory consumption, and that's never advisable if it can easily be avoided.

As far as performance is concerned, JOINs and row-count are more likely to slow query performance than the difference in selected columns, but inefficiencies have a habit of compounding later on in projects. ie. You may have no performance issues with a test-bed application but when things scale, or data is accessible only over restricted bandwidth of a network that's when you'll be pleased you wrote explicit SELECTs to start with.

Note that if you're just writing a one-off query to check some data I wouldn't worry, but if you're writing a query for a codebase that might be executed often, it pays to write good queries and, when necessary consider Stored Procedures.

Matt Coubrough
  • 3,739
  • 2
  • 26
  • 40