What is faster SELECT * or SELECT `field` when only requiring `field`

I don't want to assume here, I've been bitten/proven wrong be开发者_运维知识库fore.

Any help would be appreciated

---

SELECT field is faster than select *.

Because if you have more than 1 field/column in your table then select * will return all of those, and that requires network bandwidth and more work for the database to fetch all the other fields. But if you only require one field/column, the database load is less and it doesnt need to transmit unneeded information and thus take bandwidth resources uncesseraly.

---

Further to @AntionoP's answer, another benefit from using SELECT field is that if the field is indexed, MySQL can return the value directly from the index without needing to lookup the data row to get the result.

---

Select * is said to be slower that select <field list>, because the * required an extra lookup to get all columns.
Further, if that table contains more columns (that you are not interested in), why get them only to ignore them?

---

Select * will retrieve all the columns which might be very big, specially if you have blob's. The golden rule is to select the fields that you need.

---

You can read

• Why not to use SELECT * ( ALL ) in MySQL
• and then, the more general What is the reason not to use select *

---

Since you mention mysql, I just tested that "SELECT field" is the faster one. Why don't you just test it by yourself?

On Linux:

time (echo "SELECT * FROM table" | mysql -u username --password=passwd database > /dev/null)

time (echo "SELECT field FROM table" | mysql -u username --password=passwd database > /dev/null)

---

When using SELECT * in a nested query, sometimes the query optimizer removes the fields that you don't use in subsequent queries. So in that case it wouldn't matter. But do you trust the query optimizer of MySQL? The only way to know for sure if the SELECT * doesn't add unnecessary fields to the query plan is to directly compare both query plans by outputting them using EXPLAIN (..query..).

That said, the easiest way to be really sure is just not to use SELECT * in production queries.