Does this affect performance if the table contains many unused / unallocated columns?

Question

Background: I have a table called cars in which there are a lot of used cars, about 1 million rows. The table has a little over 170 columns. The table is indexed by individual columns only. Most columns are logical (e.g. has_automatic_gearbox, etc.), and the rest are strings and numbers (e.g. color and price). Cars are shown in a view where I use about 80 columns out of a total of 170.

My question: Therefore, my question is whether it affects performance, whether I select only 80 columns from the table when performing a search, or, on the other hand, I made a new table ONLY consisting of the 80 columns that I need, instead 170 columns? In other words, does performance matter that the table contains columns that are not selected?

Answer 1

Andomar is right in his comment that "it depends." However, if you ask a question, it is something like this:

Can the number of columns in a table affect selected queries?

Then the answer is YES . Regardless of whether they are "redundant" or "unused", we are talking about database design and has nothing to do with the issue of performance.

All things being equal, a row in a table with 100 columns will take up more space than a row in a table with 10 columns. Since the rows will be larger, your server will have to work (relatively) harder than an equal number of rows in a wider table than in a smaller table.

Things like pagination are also more common in tables whose rows take up more space.

If your question (and I think it might be more in line with what you ask)

If all things are equal, would a query of pulling 80 columns from a table with 170 columns be slower than a query of pulling 80 columns from a table with 80 columns?

Then the answer should be NO .

Answer 2

You can also read the answers to a similar question on dba.SE:

Do the number of columns in a particular table affect the performance of a query, when querying on a subset of that table? 

In short: yes, it is because non-selected columns usually live in the same disk blocks as the selected columns, so they will still be read.

Answer 3

Most of the attributes of your 1E6 vehicles are likely to depend on some hidden factors, such as {brand, model, make, model_version}. You can drop these attributes into a separate table and refer to this table in the table of the main vehicles. In the end, the table of your main cars will contain only attributes related to a specific instance of the car model (for example: {number, price, build_date, buy_date, (possibly) color}.)