0

Is there a performance difference if the number of columns that are selected are different for a given table , say if I use select * instead of select col1,col2 based on a indexed key or primary key?

Jibin Mathew
  • 4,816
  • 4
  • 40
  • 68
  • Selecting fewer columns may be faster since you're returning less data. – AdamMc331 Oct 27 '15 at 04:41
  • You mean to say obtaining more columns has a bottleneck? Or is it based on networking data? – Jibin Mathew Oct 27 '15 at 04:52
  • Possible duplicate of [Why is SELECT \* considered harmful?](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – Vishwanath Dalvi Oct 27 '15 at 05:04
  • @JibinMathew I wouldn't call it a bottleneck necessarily, as removing columns isn't going to increase a ton, but it will help. Consider a really large amount like one million rows and you have three columns of strings but you only need one. You've just requested two million unnecessary strings. – AdamMc331 Oct 27 '15 at 05:18

2 Answers2

4

As McAdam331 mentioned, selecting fewer columns may result in faster retrieval of data. Also to be noted is that if your query can fetch all the data it needs directly from an index, the table is not even touched and data is retrieved much faster.

For example:

create table test1 (id int, firstname varchar(50), lastname varchar(50), age int);
create index idx_text1_lastname_age on test1 (lastname, age);

select lastname, age 
from test1 
where lastname = 'Smith' and age between 20 and 30;

In the query above, lastname and age are both available in the index. Therefore, MySQL is likely to not touch the table at all to fetch this information.

select * from test1
where lastname = 'Smith' and age between 20 and 30;

This query above will look up the index to find ids matching the where criteria and then look up table to additionally fetch firstname and id. So this query will take a little more time than the previous one.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
1

Though, it totally depends on the amount of data your table hold but it is always considered a bad practice to fetch all columns instead of desired ones.

See this :

Why is SELECT * considered harmful?

Community
  • 1
  • 1
Sagar Joon
  • 1,387
  • 14
  • 23