0

Possible Duplicate:
Can select * usage ever be justified?

Curious to hear this from folks with more DBA insight, but what performance implications does an application face from when you see a query like:

select * from some_large_table;

You have to do a full table scan since no index is being hit, and I believe if we're talking O notation, we're speaking O(N) here where N is the size of the table. Is this typically considered not optimal behavior? What if you really do need everything from the table at certain times? Yes we have tools such as pagination etc, but I'm talking strictly from a database perspective here. Is this type of behavior normally frowned upon?

Community
  • 1
  • 1
randombits
  • 47,058
  • 76
  • 251
  • 433
  • Usually, when databases are concerned, the big O notation refers to the number of Disk IO's, not arithmetic operations – Armen Tsirunyan Mar 04 '11 at 14:30
  • 1
    Performance implications aside, I'd never recommend using `select * from ...`. Why? Your query isn't future proof. Imagine you have 4 columns today. You expect 4 columns from your query. Now imagine some day in future a column gets added, or removed, or even two columns get swapped in that table. All hell might break loose. So, it's better to explicitly state in your `selects` all columns you need. No more, no less. – darioo Mar 04 '11 at 14:32
  • @Armen Tsirunyan: discussable point of view. The well designed and tuned server will always keep indexes in memory. So fetching something from B-Tree index will still take O(logN) even though there were no disk IO have been performed. – zerkms Mar 04 '11 at 14:33
  • @zerkms: Discussable, indeed, that's why I wrote "usually" :) – Armen Tsirunyan Mar 04 '11 at 14:34
  • @darioo: columns swapped. So? Point to columns using their names, not position. – zerkms Mar 04 '11 at 14:34
  • @zerkms: true. I forgot to add in my comment that I've seen code with `select * from ...` and results were interpreted by position, not by column names. This is when column swapping will wreak havoc. – darioo Mar 04 '11 at 14:37
  • @Armen Tsirunyan: Also, guys, that question is about justification of SELECT * (emphasize on *) and this question is about selecting without `where` clause (full table query). So I vote for reopen – zerkms Mar 04 '11 at 14:38

3 Answers3

1

What happens if you don't specify columns, is that the DB Engine has to query the master table data for the column list. This query is really fast, but causes a minor performance issue. As long as you're not doing a sloppy SELECT * with a JOIN statement or nested queries, you should be fine. However, note the small performance impact of letting the DB Engine doing a query to find the columns.

Simeon
  • 5,519
  • 3
  • 29
  • 51
  • "What happens if you don't specify columns, is that the DB Engine has to query the master table data for the column list." --- so what? It is not a bottleneck when you're fetching whole table. I even don't mention that tables metainformation is almost always is cached in memory. – zerkms Mar 04 '11 at 14:36
0

MySQL server opens a cursor on server-side to read that table. The client of the query may read none or all records and performance for the client will only depend on the number of records it actually fetched. Also the performance of the query on server-side can acutally be faster than query with some conditions as it involves also some index reading. Only if client fetched all records, it will be equivalent to full table scan.

Andrey Adamovich
  • 20,285
  • 14
  • 94
  • 132
0
  1. Selecting more columns than you need (select *) is always bad. Don't do more than you have to
  2. If you're selecting from the whole table, it doesn't matter if you have an index.

Some other issues you're going to run into is how you want to lock the table. If this is a busy application you might not want to prevent locking entirely because of the inconsistent data that might be returned. But if you lock too tightly it could slow the query further. O(n) is considered acceptable in any computer science application. However in databases we measure in time & number of reads/writes. This is a huge number of reads and will probably take a long time to execute. Therefore it's unacceptable.

kelloti
  • 8,705
  • 5
  • 46
  • 82