SQL, Why is looking up data explicitly faster than SELECT *

Question

I was reading Django Book and came across interesting statement.

Notice that Django doesn’t use SELECT * when looking up data and instead lists
 all fields explicitly. This is by design:
 in certain circumstances SELECT * can be slower,

I got this from http://www.djangobook.com/en/1.0/chapter05/

So my question is can someone explain me why SELECT * can be slower, than call every single column explicitly. Would be good if you can give me some examples. Or if you think the opposite (it doesn't matter), can you explain why?

Update:

That's the table :

BEGIN;
CREATE TABLE "books_publisher" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(30) NOT NULL,
    "address" varchar(50) NOT NULL,
    "city" varchar(60) NOT NULL,
    "state_province" varchar(30) NOT NULL,
    "country" varchar(50) NOT NULL,
    "website" varchar(200) NOT NULL
);

And that's how Django will call SELECT * FROM book_publisher:

SELECT
    id, name, address, city, state_province, country, website
FROM book_publisher;

possible duplicate of [Why is SELECT * considered harmful?](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful) — xdazz, Aug 30 '12 at 03:14

score 2 · Accepted Answer · answered Aug 30 '12 at 14:56

2

performance (will matter only if you are selecting less columns than there are in the table
I am not sure about how Django works; but in some languages/ db drivers "select *" will cause an error if you change the table schema (say add a new column). This is because the DB driver "caches" the table schema and now its internal schema does not match the table schema.

answered Aug 30 '12 at 14:56

Dharmendar Kumar 'DK'

2,082
17
18

Thank you very good point about why 'select *' could cause error!! – Vor Aug 30 '12 at 20:16

score 1 · Answer 2 · answered Aug 30 '12 at 03:13

1

If you have 100 columns, SELECT * will return the data for all columns. Listing the columns explicitly will reduce the columns returned, therefore reducing the amount of data transmitted between the server and application.

answered Aug 30 '12 at 03:13

Joey Gennari

2,361
17
26

score 0 · Answer 3 · answered Aug 30 '12 at 03:13

0

This is clearly not faster in many case, and when one of them is faster, it is by a slight margin: check by yourself, benchmarking a lot of queries :)

It might be faster to select only some columns in some case, including when you select only column that are on a combined index, avoiding the need to read the whole row, and also when you avoid accessing BLOB or TEXT columns on MySQL.

And naturally if you select less column you will transfer less data between MySQL and your application

answered Aug 30 '12 at 03:13

Parallelis

699
3
6

1

Well it is obvious that if I select less column I will transfer less data. But what I'm asking is why Django does not use SELECT * and instead call each column explicitly. And in the book from oficial web site it says that explicit is better than implicit? Better why? – Vor Aug 30 '12 at 03:21

score 0 · Answer 4 · answered Aug 30 '12 at 03:29

0

I think in this exact case there will be no performance difference, this is exactly that in certain circumstances SELECT * can be slower is all about.

answered Aug 30 '12 at 03:29

Petr Abdulin

33,883
9
62
96

SQL, Why is looking up data explicitly faster than SELECT *

4 Answers4