0

I have seen several questions comparing select * to select by all columns explicitly, but what about fewer columns selected vs more.

In other words, is: 

SELECT id,firstname,lastname,lastlogin,email,phone

More than negligibly faster than:

SELECT id,firstname,lastlogin

I realize there will be small differences for more data being transferred through the system and to the application, but this is a total data/load difference, not a cost of the query (larger data in the cells would have the same effect anyway I believe) - I'm only trying to optimize my query, as I will have to load ALL the data at some point anyway...

When my admin user logs in, I'm going to load the entire user database into a cache, but I can either query only critical data upfront to shave some execution time, or just get everything - if it works out roughly the same. I know more rows equals longer query execution - but what about more selected values in my query?

MJHd
  • 166
  • 1
  • 10
  • Does this answer your question: https://stackoverflow.com/questions/25093187/is-it-bad-for-performance-to-select-all-columns – VBoka Feb 05 '20 at 07:34
  • It's VERY VERY close, however, when he mentions that it wouldn't be worth returning to the same page to re-ingest the same data/do the same query, he implies I think we will be waiting for that to finish, in my case this will all be down time, after the first query, my application has some time to kill, it could very well be making async requests with the spare time at login, so the TOTAL query length is not relevant to me - only what I can save (if anything) upfront... – MJHd Feb 05 '20 at 07:39
  • Always name, and properly qualify, all the columns you actually want returned. – Strawberry Feb 05 '20 at 08:07
  • Please provide `SHOW CREATE TABLE`. The datatypes _may_ matter. – Rick James Feb 06 '20 at 00:21
  • "load the entire user database into a cache" -- This is often a mistake; please provide details. – Rick James Feb 06 '20 at 00:22
  • @RickJames - all is well, thanks for your concern – MJHd Feb 11 '20 at 15:12

1 Answers1

0

Under most circumstances, the only difference is going to be slightly larger data for these fields and the additional time to fetch them.

There are two things to consider:

  1. If the additional fields are very big, then this could be a big difference in performance.
  2. If there is an index that covers the columns you actually want, then the index can be used for the query. This could speed the query in the database.

In general, though, the advice is to return the columns you want to the application. If there is complex processing, you should consider doing that in the database rather than the application.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Sorry for taking so long to respond - I *do* want all the data, but only some of it is visually critical up front, I'm looking to optimize the perceived (and total if possible) speed and responsiveness of my opening view. So when you say "want"- well, I "want" them all, but what this question is about is that I'm flexible about when it happens, and in how many queries - I just want optimum responsiveness from my application, so I'm starting with initial DB read. All I want to know is if the process of scanning every row of a DB is faster, if you SELECT fewer of the cells as you pass... – MJHd Feb 11 '20 at 15:15
  • What do you mean by "If there is complex processing, you should consider doing that in the database rather than the application" ? Could you please elaborate on that? – Ismael Oct 05 '22 at 04:49