On Wednesday 09 February 2011 00:12:51 Robert Collins wrote: > Secondly, there is some confusion about what makes queries fast in > SQL. Basically the goal has to be to reduce the amount of data being > examined as rapidly as possible - to use the most selective index > possible; our schemas need to be compatible with that goal, or we > can't query efficiently.
I've made some queries over 50% faster by just removing unnecessary joins to very large tables. > Because of this, when dealing with two separate but highly selective > indices on joined tables, it can be *faster* to do two queries and > union them, than to do a single query with an OR clause: in principle > the query planner can figure this all out, but in practice - well in > practice we need to measure /everything/ we do :). So when we see a > UNION query, don't assume its a bad query builder artifact - it may > be, *or* it may be a solution to querying quickly with our schema. bug > 714383 has details on a case where we should add a union in bug search > because of this. One thing I've noticed during my own optimising hack sessions is that it's very hard to work out *why* a query was written in a particular way. So, I'd love it if everyone could add some comments in the code to explain why a query is constructed in a particular way. For example, * why is it pre-joining, what needs that pre-join? * why are you using a union when an OR would suffice (apropos Rob above) or anything else that's noteworthy. We should also be a bit more diligent about asking about queries and their performance in reviews. > For the same root causes - constraining the intermediary tables that > are created, and the amount of data being processed, I'm growing more > and more concerned about the impact of wide queries - 'prejoins' on > our system; I'm starting to think we should have a default of set > based eager loading rather than wide-query eager loading : what do you > think? Can you give a concrete example? J _______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
