Is there any (simple? har!) way to optimise a particular SQL query?
At this stage, I'm more interested in making the query more readable, so
I've started hunting down references about relational theory to see if I
can use relational algebra to manipulate the query into a "shorter" form
(I believe the mathematical term is "simplify").
Since I've got somewhere between two days and two lifetimes' worth of
reading ahead of me, would it be too presumptious to ask the list if
anyone can recommend a method, tool or book that would help me out?
At present, the one query I'm struggling with is a mish-mash of unions,
uniques and subselects. I'm sure there's an easier way, but I just can't
get the tables in my head to morph themselves the way I want them to.
The "explain" of my query is 38 rows long!
In my perfect world, there'd be some tool available where I could
provide the table definitions and my sample query, at which time the
tool would spit out a magically optimised query that provides exactly
the same results with far fewer linear scans, subselects and unique
filters. Heck... perhaps it could even suggest what indices I should add
to my tables to improve the query plan.
But it's not a perfect world, and I'm just a clueless newbie in the land
of relational theory, with no idea of the caveats that cover trying to
apply relational theory to real-world SQL!
Any suggestions on general strategies in producing sql queries? eg: is
it better to apply filters closer to the data or closer to the end
result table ("deeper" or "shallower" in the subselect tree)?
