Correlated Subqueries are said to be “inefficient and likely to be slow,” but that doesn’t mean I’m not glad to have learned of them.
Bits Of MySQL Query Syntax I’ve Learned This Week
Watching the WordPress hacker list this week, a couple messages related to selecting information about users schooled me on MySQL syntax. I obviously knew the following would work, but I’d previously used the UNION syntax in similar situations and somehow hadn’t thought of writing it this way:
| ``` SELECT (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'first_name' AND user_id = 2) AS FIRST, (SELECT meta_value FROM wp_usermeta WHERE meta_key = 'last_name' AND user_id = 2) AS LAST, wp_users.* FROM wp_users WHERE wp_users.ID = 2 ``` |
That’s much cleaner to my thinking, though I’ve no idea which is more optimal. When somebody replied asking for a solution that would work in pre-MySQL 5, this was the response:
| ``` SELECT ID, user_login, FIRST.meta_value AS fname, LAST.meta_value AS lname FROM wp_users LEFT JOIN wp_usermeta AS FIRST ON (wp_users.ID = FIRST.user_id AND FIRST.meta_key = 'first_name') LEFT JOIN wp_usermeta AS LAST ON (wp_users.ID = LAST.user_id AND LAST.meta_key = 'last_name') ``` |
And the lesson to me here is that I didn’t realize the syntax allowed us to match multiple conditions for the JOIN. Makes sense, but I just hadn’t thought of it. Thanks go to Phil Williams and Otto for tipping me to these.
Catching Bugs Before They Catch You
I got itchy about magic quotes the other day because it’s the cause (through a fairly long cascade of errors) of some performance problems and runaways I’ve been seeing lately (pictured above). But I deserve most of the blame for allowing a query like this to run at all: SELECT type, data, count(*) AS hits […] » about 300 words