Does SELECT * really take more time than selecting only the needed columns?

Question

Will it make a discernible difference in the time a website page loads? On average, my tables have 10 columns, if I just need 3 of those columns, should I just call those in the query to make it faster?

Answer 1

Will it make a discernable difference. Probably not under most circumstances. Here are some cases where it would possibly make a big difference:

• The 7 unneeded columns are really, really big.
• You are returning lots and lots of rows.
• You have a big table, are getting many rows, and an index is available on the 3 columns but not the 10.

But, there are other reasons not to use *:

• It will replace the columns based on the order of the columns in the database at the time the query is compiled. This can cause problems if the structure of the table changes.
• If a column name changes or is removed, your query would work and subsequent code might break. If you explicitly list the columns, then the query will break, making the problem easier to spot.
• Typing three column names shouldn't be a big deal. Explicitly listing the columns makes the code more informative.

Answer 2

Yes if you only need a handful of columns, only select those. Below are some reasons:

1. THE MOST OBVIOUS: Extra data needs to be sent back making for larger packets to transmit (or pipe via local socket). This will increase overall latency. This might not seem like much for 1 or 2 rows, but wait until you've got 100 or 1000 rows... 7 extra columns of data will significanly affect overall transit latency expecially if you end up having the result set having to be broken into more TCP packets for transmit. This might not be such an issue if you're hitting a localhost socket, but move your DB to a server across a network, to another datacenter, etc... and the impact will be plain as day!
2. With the MySQL query cache enabled, storing unneeded data in result sets will increase your over cache space needs--larger query caches can suffer performance hits.
3. A HUGE HIT CAN BE: If you need only columns that are part of a covering index, doing a select * will require follow up point lookups for the remaining data fields in the main table rather than just use the data from the index table.

Answer 3

Let's say you had a table with 1000 columns, and you only needed 3.

What do you think would run faster and why?

This: SELECT * FROM table_name;

or this:SELECT col1, col2, col3, FROM table_name;

When you are using * you are now holding that entire selection (big or small) in memory. The bigger the selection...the more memory its going to use/need.

So even though your table isn't necessarily big, I would still only select the data that you actually need. You might not even notice a difference in speed, but it will definately be faster.