0

I have a table contain many columns and I don't need to retrieve all these columns in every screen of my application, so I use select statement for the only columns that I need.

My question is about the technique of searching: does the database engine handle the columns I requested only, or will the other columns will be considered in the performance of the query executing?

What I mean is if I have a table contains 100 columns, when I SELECT specific column only, then will the database engine take the same time as when selecting all columns?

smile
  • 78
  • 7
XRushdy
  • 7
  • 1

1 Answers1

1

There are basically two parts to consider.

First, there's how data is lied out on disk. The smallest unit of data read generally is a page, containing one or more rows. Adding columns costs more storage per row, so retrieving a record with more columns will require the storage engine to read more pages, resulting in more disk I/O.

If the columns you select are in an index however, the row data may not even have to be read, as the column's data can be fetched from the index.

Then there's caching. Given a fixed size of memory cache, fewer rows will fit in there if you have more columns, so by adding more columns you'll have less data ready to be served from cache.

Second there's network traffic, which is affected by the columns requested. A SELECT * will have to package all row data into network packets, while selecting specific columns will only transfer the data of those columns.

More network traffic per row means less rows per second downloaded by your application.

So in short: only select the columns you're going to need, and consider including relevant columns in an index where appropriate.

smile
  • 78
  • 7
  • So you're saying that even the table contains 100 columns, when i SELECT one spesific (or SUM or any funcation) for on one column the search engine handles the query like the table has only one column?(performance of query will be like the table contains only one query or the 99 affects the operation?) – XRushdy Jun 25 '17 at 11:54