Here's one for pathological SQL programmers.
I've got a table of things called elements. They're components, sort of
like amino acids, which come together to form complex web pages -- as
nodes in trees which form parent-child relationships, sort of like
newsgroups. For example, the parent_id field points to another element.
Indent_level is there for denormalization purposes, to avoid costly
recursive issues in querying. The element_page_id allows pages to link
to other pages via elements (pretty complex, I daresay, but don't worry
about that part).
Each row must have certain information, but may only have one value (a
numeric foreign key reference) among the following: resource, location,
service, staff, subject, and element_page_id. The others are NULL.
I've marked them below betweens rows of asterisks. In this example,
only service_id actually needs to perform a LEFT JOIN to fetch
information about it:
For example:
element_id: 6563
page_id: 207
parent_id: 6561
*********************
resource_id: NULL
location_id: NULL
service_id: 22
staff_id: NULL
subject_id: NULL
element_page_id: NULL
*********************
label: NULL
label_url: NULL
element_descr: NULL
element_size: NULL
element_order: 22
indent_level: 1
What I do is assemble a query to display the elements in element_order
for a particular page_id. At one point in the query I do this:
LEFT JOIN resource r using (resource_id)
LEFT JOIN service v on e.service_id = v.service_id
LEFT JOIN location l on e.location_id = l.location_id
LEFT JOIN staff s on e.staff_id = s.staff_id
LEFT JOIN subject b on e.subject_id = b.subject_id
LEFT JOIN page pe on e.element_page_id = pe.page_id
I need to get values unique to each sort of (heterogenous) element in
order to display the assembled page. For example, the name and e-mail
of a staffperson, the title and URL of a resource, the hours of a
location, etc.
My question is how to optimize this approach. Correct me if I'm
mistaken, but a LEFT JOIN where the left side is NULL will never result
in a full table scan of the right-hand side -- so what I'm doing here
isn't terribly intensive (doing a LEFT JOIN on everything, although only
one actually applies).
Lacking the CASE statement, is there any other way to optimize this?
I'm using mySQL 3.23. Any suggestions on optimization would be appreciated.
Paul Bramscher
br******@umn.edu