which query is faster?

[mattee]

Does anyone know if it makes a diff to "select *" on a table or to select on a specific column name?

specifically, I'm trying to find out if there's a difference between the two queries:

select * from index_word where word = 'something';

select word_id from index_word where word = 'something';

I'm running queries like this a bunch of times so even a small diff would matter.

Matt.

[Anon]

They are probably right about the same speed if, say, you have this table:

ID - int - auto inc.
Name - varchar(50)
Email Address - varchar(50)
SomeText - text

Now, say you wanted to have ALL of those fields (ID,Name,Email,SomeText), there is probably NO difference between these:
select * from table;
and
select ID,Name,Email,SomeText from table;

However, if you only want to get the ID, it is likely that:
select ID from table;
is faster than
select * from table;

On a side note, the difference between them is extremely small... and personally, I find it better practice to just type out all of the columns you need to use and don't get the columns you don't need (even if I needed 3 of the 4, I would type out the entire name for all 3 instead of using *.

Hope this helps 🙂

[kparker]

Why don't you time them and see?

[[deleted]]

Getting more columns means reading and storing and sending more data.
So it's bound to be slower.

How much slower depends on how much 'useless' data is in the other columns.

If you time it you'll probably find that on small queries the difference is near zero, but on a 2Million row table with blobs in it, I'm sure the difference will be a lot bigger.