What are the potentially negative implications of using SELECT * in MySQL?

Question

Possible Duplicates:
Performance issue in using SELECT * ?
What is the reason not to use select *?

I recall reading a blog some time ago that detailed performance issues with using an asterisk to select all columns of a table in MySQL, and I can no longer find it. I'm using a combination of MySQL and PHP's PDO driver (and therefore prepared statements), and the asterisk would naturally speed up the maintenance of a lot of my SQL queries.

Would you consider it to be acceptable practice to use the asterisk? If not, what are you reasons (security/performance/standards wise)?

Answer 1

There is a list of issues with using select *.

1 - consistency of output
If your client expects certain fields and you change the layout of a table, select * will change its output if the underlying tables changes, breaking your client code.

2 - Network performance
If you only need a subset of all field in the output, select * will waste network bandwidth by sending unneeded data across the wire.

3a - Database performance
On simple tables select * may not slow performance down, but if you have blob fields (that you're not interested in) select * will fetch those as well, killing performance

3b - Memory usage on the database server
If MySQL needs to use a temporary table, select * will tax the servers, disk, memory and CPU extra by making MySQL store more data in memory.

3c - On InnoDB covering indexes cannot be used
On InnoDB if you select fields that are indexed, MySQL needs never to read the actual table data, it just reads the info straight from the index, select * kills this optimization.

4 - Clarity of code
select * gives the reader of a query no info on which fields will be retrieved from the server. select name, address, telephone from ... makes it instantly clear what data we are dealing with.
If your tables are in Zulu you can even do

select
  igama as name
  ikheli as address
  ....

Which is much more useful than the original names for the 99.7% of the people that don't speak Zulu.

5 - Don't hack code, craft it
Select * is just a quick hack to make stuff work.
But when you write code you're supposed to have an idea what you're doing, make that explicit, select what you need, give fields meaningful name using aliases if needed.
Tweak your code to select only what you need.
If I see a select * in a code review, I mark it down sight unseen because it's a code smell.

Hope this helps.

Answer 2

The points of naming your columns instead of using the * are:

• Intentionality: your program should probably know what columns you're fetching
• Performance: why transmit from mySql to your program columns you don't need? That just burns bandwidth.

If you know you need all the columns, you're meeting the above two criteria. There's nothing magic here, just good software development.

But, keep in mind that your table schemata might change after you write your program. If that happens, your use of * starts breaking the intentionality criterion.

Answer 3

Far more important than performance is clarity. Reading a statement like SELECT * in some source code gives me absolutely no information. To find out what columns the result of that query contains I have to either look into the database documentation (if any exists at all) or have to take a look at the database itself.

The coding styles in my company demand full statements, mainly for this reason. INSERT INTO must contain a list of columns, SELECT * is forbidden, and so are e.g. GROUP BY 1 or ORDER BY 1.