0

I am working on converting a prototype web application into something that can be deployed. There are some locations where the prototype has queries that select all the fields from a table although only one field is needed or the query is just being used for checking the existence of the record. Most of the cases are single row queries.

I'm considering changing these queries to queries that only get what is really relevant, i.e.:

select * from users_table where <some condition>

vs

select name from users_table where <some condition>

I have a few questions:

  1. Is this a worthy optimization in general?
  2. In which kind of queries might this change be particularly good? For example, would this improve queries where joins are involved?
  3. Besides the SQL impact, would this change be good at the PHP level? For example, the returned array will be smaller (a single column vs multiple columns with data).

Thanks for your comments.

MyName
  • 2,136
  • 5
  • 26
  • 37
  • 2
    yes, all of them, yes –  Sep 20 '15 at 21:09
  • 1
    Similar(possible duplicate) question http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful –  Sep 20 '15 at 21:16

2 Answers2

2

If I were to answer all of your three questions in a single word, I would definitely say YES.

DirtyBit
  • 16,613
  • 4
  • 34
  • 55
1

You probably wanted more than just "Yes"...

  • SELECT * is "bad practice": If you read the results into a PHP non-associative array; then add a column; now the array subscripts are possibly changed.
  • If the WHERE is complex enough, or you have GROUP BY or ORDER BY, and the optimizer decides to build a tmp table, then * may lead to several inefficiencies: having to use MyISAM instead of MEMORY; the tmp table will be bulkier; etc.
  • EXISTS SELECT * FROM ... comes back with 0 or 1 -- even simpler.
  • You may be able to combine EXISTS (or a suitable equivalent JOIN) to other queries, thereby avoiding an extra roundtrip to the server.
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • +1 and "accepted" for giving some justifications. Regarding the "select count(*)", does MySQL optimize quer query specifically for it? Is it equivalent to making a "select count(pk_field)"? – MyName Oct 15 '15 at 15:43
  • `COUNT(x)` checks `x` for being `NOT NULL`; hence that is potentially slower than simply `COUNT(*)`. – Rick James Oct 15 '15 at 22:37
  • I should have said `EXISTS SELECT * ...`, not `EXISTS SELECT COUNT(*) ...`. Yes the `*` (or `1`) is optimal. – Rick James Oct 15 '15 at 22:38