1

I'm wondering if there is significant difference in performance between

select * from table where something

and

select column from table where something
Dimitar Georgiev
  • 11
  • 4
  • possible duplicate of [Select \* sql query vs Select specific columns sql query](http://stackoverflow.com/questions/5020561/select-sql-query-vs-select-specific-columns-sql-query) –  Nov 15 '14 at 09:35
  • 1
    possible duplicate of [Why is SELECT \* considered harmful?](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) – Salman A Nov 15 '14 at 09:37
  • Sorry for the duplicate and thanks to everybody. – Dimitar Georgiev Nov 15 '14 at 09:49
  • What I didn't see so far (even in the duplicates) is a mention to the storage and execution model. MySql is a row-store: internally it processes entire rows, regardless of their actual usage in the query. So there can be a difference, but it is rather marginal compared to the difference you'd find in column-store engines, where only the columns actually used are touched. – cornuz Nov 15 '14 at 10:46
  • For those who wants more expressive answer here is the [explanation](http://discoversql.blogspot.com/2012/04/select-vs-select-columns-sql-server.html) - see the Summary. – Dimitar Georgiev Nov 15 '14 at 10:10
  • Thank you @cornuz. The way MySQL process the rows is actualy the core of the answer. In the Summary of the [article](http://discoversql.blogspot.com/2012/04/select-vs-select-columns-sql-server.html) you can find exactly the same conclusionс. – Dimitar Georgiev Nov 16 '14 at 08:53
  • Especialy for Philip G and Shree who are eager for negative voting! Thank you guys! Be happy! _** Summary 1.SQL Sever by default copies all the columns to buffer pool, irrespective of column used in the SELECT query.2. Though you don’t find much performance impact in the SQL Server query processing, it is good to use only the columns you needed as this will increase the overall system performance.**_ – Dimitar Georgiev Nov 16 '14 at 09:01

3 Answers3

0
SELECT * FROM table WHERE something

Will retrive all columns in that table where as 

SELECT column FROM table WHERE something

would only retrive that column.

This means that the later would be faster. But if that would be a SIGNIFICANT diference depends on you table size.

You can read this answer on a similar question for more info

Community
  • 1
  • 1
Philip G
  • 4,098
  • 2
  • 22
  • 41
0

Yes there is performance difference.

SELECT * FROM someTable WHERE something

will be heavier as compared to 

SELECT column FROM someTable WHERE something

Because the first one will have to process all the columns while the second one will have to process only one. You should always prefer the second one.

For further detail, I would refer you to What is the reason not to use select *?

Community
  • 1
  • 1
Kamran Ahmed
  • 11,809
  • 23
  • 69
  • 101
0

Here's a little benchmark I did to compare SELECT * vs SELECTing individual columns. It's a simplified code with 100 iterations in the loop and in my test I queried only what I needed, 25 columns out of 34, vs SELECT *

Results: SELECT * took on average 4.8sec to complete, and SELECT individual columns took 3.2sec ! This is very significant. So indeed SELECT * is much slower. However, on a smaller query from a table with 4 columns, selecting all vs * gave virtually the same benchmarks. So in my tests, SELECT * will have a performance impact on complex queries from big tables with a lot of columns.

$start_time = microtime(true);
for ($x = 0; $x < 100; $x++) {
$results = $dbp->multi(
      "SELECT t1.id, t1.name, t2.field, t2.something, t2.somethingelse "
      . "FROM table1name t1 INNER JOIN table2name t2 ON t2.id = t1.id "
      . "ORDER BY title ASC LIMIT 1, 50");
}
$ms = (number_format(microtime(true) - $start_time, 4) * 1000);
$end_time_sec = floor($ms / 60000) . 'm:' . floor(($ms % 60000) / 1000) . 's:' . str_pad(floor($ms % 1000), 3, '0', STR_PAD_LEFT) . 'ms';
echo "$ms ms / $end_time_sec";
synkro
  • 414
  • 7
  • 9