Slightly more advanced select query

[janroald]

Typical query :
'select a.id, a.this, a.that, a.bref, b.something from a,b where a.bref = b.id';

Problem is that if the relation between a and b isn't found the query returns nothing.
If i know that i'm quering with a.bref value of '-1', and that the corresponding b.id doesn't exist, how can i alter my query to check if a.bref = '-1' and in that case return me a row where "something" contains ex. 'not found' ?

Essentially I just want to get the values from table a even if the relations with b doesn't exist.

Any clues would be greatly apprechiated. 

[effigy]

Try[tt] select a.id, a.this, a.that, a.bref, b.something from a LEFT JOIN b ON a.bref = b.id[/tt].

[janroald]

That works great, thanks!

Modified it a bit so that the comparison between a and b isnt neccesseary if a.bref <> '-1'
I guess this will make it quicker since the ON clause will "short-circuit" on first test and table b wont be checked if a.bref = '-1'

select a.id, a.this, a.that, a.bref, b.something from a LEFT JOIN b ON (a.bref <> '-1' and a.bref = b.id)

Wouldn't you agree?

[effigy]

Makes sense. I didn't think about putting more than one condition in ON(). Bravo :)

[fenway]

Be careful about putting WHERE clauses into ON conditions... there are index use implications, and if you ever use an inequality, well, you'll see what happens.  The "short-circuit" may actually be slower in many cases -- and there are only a few circumstances when you actually _intend_ to put it there.  I don't think this is one of these.  Post the EXPLAIN for both, and let's talk.

[janroald]

Fenway :
Well, the typical use for theese queries are to get a row from a table, with the references "translated".
Example for the kids: table with invoice's and in that table references to customer id's. You want the invoice information and the full name of the customer without making two queries.
Normally, an invoice always has a valid customer id, and hence this ON-clause would not be needed - unless someone has deleted the customer record (dr.evil) :-)

The reason i need this ON clause, is because i [b]know[/b] that in some cases, i [b]will[/b] look for an b.id, that isn't there.
Say table a is a holding crm-lead information, table b is customers. When inserting a lead row, the user can either choose to say "this lead is about _that_ customer" or "this lead is about ALL customers" or this lead is currently not about any customer, but could be". For case 1 we put in the customer id as a reference. In case 2 we put in that the customer id is "0", and in case 3 we say "-1". Now "0" and "-1" does of course not exist in table b, because then we would have to put in two default rows in table b every time we duplicate the database, and it would be a drag when doing listings etc. unless we filter them out. With such an ON clause in the query, this gets solved in the code. There are probably smarter ways to do this query though, but I don't know them...

Now I see what you mean about it might beeing slower, and I think you are right. 
---------test1------------test2--------
ON (a.bref <> '-1' and a.bref = b.id)

say 10% of the  a.bref's are '-1', then in 9 out of 10 cases you will have 2 tests instead of just one, and just one time, it "short-circuits" and avoids the potentially slower test2. Test 1 then has to be 10?(no time for math now :-) ) times faster than test2 for the short-circuit to be efficient.

[shoz]

I don't know if I've followed everything you've posted but note that you should use the query that effigy posted originally.

There should not be any performance gain by JOINing on (a.bref <> -1 .....). It's more likely to slow down the query as you've added a test. Further slow downs are also more than likely to occur if you decide to ORDER and LIMIT the results because MYSQL won't be able to use indexes to do the comparison.

EDIT: To be clear, even if all of the a.bref's are -1 it's more likely to be slower.

[janroald]

[quote=shoz]I don't know if I've followed everything you've posted but note that you should use the query that effigy posted originally.[/quote]
That was what i too discovered in my last post.

[quote=shoz]EDIT: To be clear, even if all of the a.bref's are -1 it's more likely to be slower.[/quote]

If mysql uses "short-circuit" comparison ( havent found docu, but prob. yes ), I dont see how this statement can be correct. The test "a.bref <> '-1'" must be a quicker test than "a.bref = b.id" since the latter needs to lookup another table. Hence; if most a.bref's are '-1', only first test will be evaluated and you would have effectively replaced a "heavy" test with a "light" test.

[shoz]

[quote author=janroald link=topic=124880.msg518307#msg518307 date=1170275765]
[quote=shoz]I don't know if I've followed everything you've posted but note that you should use the query that effigy posted originally.[/quote]
That was what i too discovered in my last post.

[quote=shoz]EDIT: To be clear, even if all of the a.bref's are -1 it's more likely to be slower.[/quote]

If mysql uses "short-circuit" comparison ( havent found docu, but prob. yes ), I dont see how this statement can be correct. The test "a.bref <> '-1'" must be a quicker test than "a.bref = b.id" since the latter needs to lookup another table. Hence; if most a.bref's are '-1', only first test will be evaluated and you would have effectively replaced a "heavy" test with a "light" test.
[/quote]

You make a good point which I'd have to agree with. Even though MYSQL won't use indexes for the comparison, if it's going through the entire table regardless it could be faster.

EDIT: I'd still have concerns if the results are to be ORDERed or if there's another WHERE clause restriction however. The fact that you can't use an index with <> is likely to come up as an issue more likely than not with further complexity.

[fenway]

[quote author=janroald link=topic=124880.msg518307#msg518307 date=1170275765]
If mysql uses "short-circuit" comparison ( havent found docu, but prob. yes ), I dont see how this statement can be correct. The test "a.bref <> '-1'" must be a quicker test than "a.bref = b.id" since the latter needs to lookup another table. Hence; if most a.bref's are '-1', only first test will be evaluated and you would have effectively replaced a "heavy" test with a "light" test.
[/quote]
Not exactly... because this type of ON clause results in a eq_ref select_type in the EXPLAIN -- you already have the uid to match from the "outer" table, and the "inner" table records being joined are examined directly from the PK (or another appropriate index).

Also, having it the where clause will also "solve" your problem in code, since the records will be discarded.  I'd still like to see the EXPLAIN output.  And shoz is right about index usage with <>.

[janroald]

This is getting quite interesting :-)
I'll get back to you both tomorrow with EXPLAINs etc. :-)

[janroald]

So no use for the short-circuit then, i see.

EXPLAIN
+----------------+--------+---------------+---------+---------+---------------------+------+-------+
| table          | type  | possible_keys | key        | key_len | ref                          | rows | Extra |
+----------------+--------+---------------+---------+---------+---------------------+------+-------+
| a              | ALL  | NULL              | NULL    |  NULL    | NULL                      |    7 |      |
| b              | eq_ref| PRIMARY        | PRIMARY |4        | ak.kontaktpersonref |    6 |      |
+----------------+--------+---------------+---------+---------+---------------------+------+-------+

[quote=fenway]Also, having it the where clause will also "solve" your problem in code, since the records will be discarded[/quote]
Are you saying this query would be better solved with a where clause?

I'm also planning to use several "join on" clauses with a couple of other tables and primary key sorting and limit...

[shoz]

[quote author=janroald link=topic=124880.msg518816#msg518816 date=1170323967]
So no use for the short-circuit then, i see.

EXPLAIN
+----------------+--------+---------------+---------+---------+---------------------+------+-------+
| table          | type  | possible_keys | key        | key_len | ref                          | rows | Extra |
+----------------+--------+---------------+---------+---------+---------------------+------+-------+
| a              | ALL  | NULL              | NULL    |  NULL    | NULL                      |    7 |      |
| b              | eq_ref| PRIMARY        | PRIMARY |4        | ak.kontaktpersonref |    6 |      |
+----------------+--------+---------------+---------+---------+---------------------+------+-------+
[/quote]

The EXPLAIN will only show so much. With the LEFT JOIN as mentioned, MYSQL will be going through the entire table with or without the test for (a.bref=-1). The EXPLAIN won't show how MYSQL is going to test the <> condition however. MYSQL could/should still perform that comparison before trying the a.bref=b.id condition.

Note that my comments are in reference to the possibility that depending on the ratio of the values in table "a" that the query could be faster with the "a.bref <> -1" condition as you said, not that you should use it. I do realize that you implied that you wouldn't.

[quote author=janroald link=topic=124880.msg518816#msg518816 date=1170323967]
[quote=fenway]Also, having it the where clause will also "solve" your problem in code, since the records will be discarded[/quote]
Are you saying this query would be better solved with a where clause?
[/quote]

If you don't want a result that contains "a.bref"s with a -1 then you should move the test to the WHERE clause.

[fenway]

There is an explain extended, though I think that's more useful with views and such.  But you can see from the explain posted above that it's not going to even bother evaluating the "other" join condition until it's already "joined"....

[shoz]

[quote author=fenway link=topic=124880.msg518955#msg518955 date=1170340623]
There is an explain extended, though I think that's more useful with views and such.  But you can see from the explain posted above that it's not going to even bother evaluating the "other" join condition until it's already "joined"....

[/quote]

Internally MYSQL could (or again, should) still end up evaluating the <> condition first. The EXPLAIN has to show how it's going to do the JOIN between the two tables and because it can't show what it'll do with the first condition doesn't give any indication IMO as to which will be done first.

[effigy]

Is it possible to do the JOIN only based on the ids, and then do the unequality test with a HAVING?

[shoz]

[quote author=effigy link=topic=124880.msg518994#msg518994 date=1170342019]
Is it possible to do the JOIN only based on the ids, and then do the unequality test with a HAVING?
[/quote]

If the results shouldn't contain a.brefs with a -1 then you could. However, if that were the case I think it would be better to put the test inside the WHERE clause because even though an index won't be used it would allow MYSQL to remove those rows before doing the JOIN. While using HAVING forces MYSQL to do the JOIN and evaluate all expressions that may be in the column list with all the rows before removing them.

[fenway]

Hmmm.... I'll ponder that one.

[janroald]

[quote author=shoz link=topic=124880.msg518914#msg518914 date=1170336687]
[quote author=janroald link=topic=124880.msg518816#msg518816 date=1170323967]
[quote=fenway]Also, having it the where clause will also "solve" your problem in code, since the records will be discarded[/quote]
Are you saying this query would be better solved with a where clause?
[/quote]
If you don't want a result that contains "a.bref"s with a -1 then you should move the test to the WHERE clause.
[/quote]

I still want a result with values from table a, even though the relation with table b isnt found. a.brefs with a value of '-1' is constructed to tell me that there is no relation,or the relation commits to every row in table b. To my limited experience, I havent found a way to do this with a WHERE clause.

[fenway]

Yeah, but since your left joining in b, you'll always get all the a's, no matter what....