MySQL - "select" speed

I was wondering if it's faster to select all fields from a table like:

SELECT *

or select just the ones you really need:

SELECT field1, field2, field3, field4, field5...

assuming the table has around 10 fields, and I only need like 5 开发者_如何学Goor 6 for this operation

Wherever possible, it's faster to select precise fields. Your intuition is correct: anything you can conceptualize as wasted machine time is likely indeed wasted work, though the magnitude is always subject to question. This particular example is one reason generated code (through ORM) is so popular! With ORM, most basic and repetitive optimization stuff is in the hands of the ORM itself, and custom hand-written queries are the exception rather than the rule.

You should always select the exact fields, or else you could face enormous performance hits when you modify your table. Think about what would happen if you decide to add a blob to that table. You might accidentally select megabytes of unneeded data.

There is a slight nuance. It might be better for the query cache to have only a limited number of queries, so when you sometimes need only field1, 2 and 3, and some other times you may need 4 as well and 4 is only a small field, it might be better to always select 1, 2, 3 and 4, even if you need only the first 3. You still should never use * though.

Choosing your fields can have big impacts on speed. When you do a select * and some of the fields are BLOB or TEXT you will big negative impacts. Check this documentation page and serach 'avoid using select *'.