Hello Alex... Alex Hudson escribió: > Hi Terry, > > You noted in a private mail one of my queries was wrong - you're right. > > The intention of switching to a LEFT JOIN rather than INNER is that > INNER requires that there be a value on both sides, whereas LEFT JOIN > will put a NULL on the right if there is no such matching value (if that > makes sense). I think we have to do that for situations where the > external property may or may not be present: e.g, WHERE alex.foo=50 OR > d.type=7. As I understand it, with an INNER JOIN any document not having > an 'alex.foo' property wouldn't make it into the result set, which > potentially removes other documents who do have d.type=7. >
You are right. I have noted that wrong query because you have wrote about timing of both queries but, if queries give us different results, then the 'timing' will be different and not comparable either. > So, where I had: > > testmoo NOT NULL AND > testfoo NOT NULL AND > (testfoo > 50 OR testmoo > 50) > > ... what I really should have had was: > > (testmoo NOT NULL AND testmoo > 50) OR > (testfoo NOT NULL AND testfoo > 50). > > ... which I think is the correct query. I'm not totally sure why I put > the NOT NULL in there; there was a good reason but I'm not sure what it > was. It's redundant in the above query really. > I agree it is redundant here, because testmoo cannot be greather than 50 and null at the same time. > The winner though still seems to be the corrected LEFT JOIN approach, > presumably because there are far fewer subqueries? On my test data of 1 > million documents / 2 million properties, it's a few seconds ahead of > the other queries usually - it takes about five seconds to complete. I > suspect on "real" data it would be faster still, to be honest, because > the values are much more variable and thus the intermediate result sets > should be smaller and indexes more useful. > Glad to see we are got the best with the LEFT JOIN approach. If I have another crazy idea I will tell you. Greetings.... _______________________________________________ Bongo-devel mailing list [email protected] https://mail.gna.org/listinfo/bongo-devel
