MySQL select all is bad idea

When I started writing my site with PHP/MySQL, my SQL SELECT queries would generally consist of:

SELECT * FROM tablename WHERE condition

I would then have that query spit out into an array. The next step would be to sift through and pick what I wanted. Maybe I wanted the data from the 2nd and 4th columns, so I would pull out $data[$j][2] and $data[$j][4] ($j is my counter). It works just fine, no problem... yet.



Fast forward a few months when I have implemented some new features, and replaced some old functions. I no longer need some of these columns, so let's delete them from the database, no functions are using them. Kaboom; things just went amuck. Why? Nothing was using that column. Now the data I need is in $data[$j][3] instead of $data[$j][4] and all my code is pointing to the wrong stuff.

It's possible to patch it up by going through all the code, and changing the fours into threes, but that's short-term. You should really be refining the queries to avoid such problems, by using this type of query:

SELECT columnname, columnname FROM tablename WHERE condition

This would be the smarter way to go about this. I wish I had thought of this, now I need to go look through all my code and make some changes; it's for the better though. I'm not really sure how to go about testing which is more efficient (where you run something 10k or so times and record the average time for different methods), but has this been done before?

And for the record, I think the scene in this image is a great idea.

Photo: Flickr

Godspeed.


This posted tagged as: code

Recent posts tagged as code:

• MySQL select all is bad idea

#1: by dcpatton on 07/25/08 @ 9:03PM EST

Your advice to select specific column names is very good and definitely is a good practice. However, you might also consider using the column name with the mysql_result function.

Reply to this comment.

#2: by will on 07/25/08 @ 9:26PM EST

Thanks for the comment, dcpatton. The PHP manual says that mysqli_fetch_row is faster than mysql_result. In that case it comes down to which is faster between these two options:

SELECT columnname + mysqli_fetch_row()
and
SELECT * + mysql_result(column)

Both seem to serve the same function. I can imagine that the difference between the two isn't significant, so it wouldn't be worth going through code to change it one way or the other, but it would be interesting to know nonetheless.

Name:


Website:


Enter a valid email:

(Gravatars and retaggr profiles are supported.)

Receive notification of followup comments?
Yes No

Comment:
(First time commenters: Don't post links or you'll be flagged as spam.)


Reply to main post. (To reply to an individual comment, select it above.)