Tilted Forum Project Discussion Community - View Single Post - [MySQL] My brain blew a fuse (multi-table select, joins(?))

Rawb · 05-17-2004, 09:55 PM

Well, it's tough to say exactly what is going on here, except that this query is probably not going to scale well at all, it's doing a full cross-join accross six tables and then weeding out the unnecessary rows. MySQL is sometimes good at this, but normally when you spell it out with explicit LEFT JOIN queries, it has a lot easier of a time optimizing it.

Secondly, there are no constraints to make sure that the tables are even joined correctly, so they can join any way they want and you aren't going to get the results you expected. To fix that up you are going to add a lot of statements to your WHERE clause that look like this "oj.origin_id = o.origin_id" without those, you're just going to get a hump-o-data

Okay. So, you are getting exactly what you asked for. oj.origin_id = 1, there is only one row where that is true. So without knowing how many rows in the other places actually match that, I can't tell for sure, but I'm guessing it's exactly what you asked for. To see everything that it collects (beware, the result set from this query will get huge, and really, really fast) just take everything off in the WHERE clause that doesn't look like this "oj.origin_id = o.origin_id" (you did read the second point, didn't you?)

05-17-2004, 09:55 PM	#2 (permalink)
Rawb Crazy Location: Salt Town, UT	Sorry, I'm a big JOIN fan... Well, it's tough to say exactly what is going on here, except that this query is probably not going to scale well at all, it's doing a full cross-join accross six tables and then weeding out the unnecessary rows. MySQL is sometimes good at this, but normally when you spell it out with explicit LEFT JOIN queries, it has a lot easier of a time optimizing it. Secondly, there are no constraints to make sure that the tables are even joined correctly, so they can join any way they want and you aren't going to get the results you expected. To fix that up you are going to add a lot of statements to your WHERE clause that look like this "oj.origin_id = o.origin_id" without those, you're just going to get a hump-o-data Okay. So, you are getting exactly what you asked for. oj.origin_id = 1, there is only one row where that is true. So without knowing how many rows in the other places actually match that, I can't tell for sure, but I'm guessing it's exactly what you asked for. To see everything that it collects (beware, the result set from this query will get huge, and really, really fast) just take everything off in the WHERE clause that doesn't look like this "oj.origin_id = o.origin_id" (you did read the second point, didn't you?)