Double-left-join three-table select query
Warning, SQL geekness ahead.
One of my complaints with a lot of pre-made web software (Wordpress, Drupal, etc.) is that the database overhead is high.
The argument usually goes that scaling makes it a non-issue, but that’s just a fancy cop-out saying “get faster server infrastructure and more RAM” to dismiss any performance issues. I hate it when software dictates higher costs though, it’s supposed to solve problems, not create them. In a large working environment, ease-of-use is cheaper, but in more humble projects like mine, efficiency of code is cost-saving. That’s the threshold between using a pre-made framework / CMS versus DIY.
*ahem* Anyway, getting back on track before I go into a full-on rant:
SQL statements are typically made as small queries like:
select user_id from user_tbl where user_id = $check_id;
select user_name from user_tbl where user_id = $verified_id;
Anyone remotely aware of SQL would put those into one query:
select user_id, user_name from user_tbl where user_id = $check_id;
This isn't meant to be a tutorial tho. Here's a more complex example that illustrates what I work with while creating Gameslate (breaks added for readability):
select item_source_tbl.item_id, item_name
from
(item_source_tbl left join item_earned_tbl on item_source_tbl.item_id = item_earned_tbl.item_id)
left join item_own_tbl on item_source_tbl.item_id = item_own_tbl.item_id
where
(item_points = 0 and item_cost = 0)
or
((act_points >= item_points and item_earned_tbl.user_id = 1) and item_cost = 0)
or
((act_points >= item_points and item_earned_tbl.user_id = 1) and item_own_tbl.user_id = 1);
Crazy eh? Let me break it down a little. There are three tables involved:
- item_source_tbl stores all the possible items that could be earned and bought.
- item_earned_tbl stores info about earning items. Items have a count (in points) towards earning which users accumulate into this table.
- item_owned_tbl simple table storing who owns (purchased) which items.
What the query above does is "select the name and id of all items that are earned (or required no earning) and are paid for (or free)". It could be made easier with different tables, but these tables store a bunch of other info as well for other tasks. It could also be more complex if I added sanity checks, but I'm the only one on my server with database access so I stopgate that at user input (web code).
MySQL is beautifully fast with these kind of queries, but I usually don't see this sort of thing in most code for frameworks, especially not in object-oriented code (which tends to break things down into pieces). Granted, it's hard to read, but it's efficient and plays into the strengths of the SQL server. I sometimes wonder why so many projects use SQL at all when they're going to separate into simpler queries, but I guess the answer is more about convenience than performance.
It's all great until next year I look at the code and wonder WTF it does again, even with comments. =)
It's hard to keep track of this stuff during a heatwave melting my brain.
Hello,
You should never use OR. Use UNION Instead. Find out on the web how to. Your query will be must faster.
Regards,
André