complex mysql 3.23 LEFT JOIN question

[Paul Bramscher]

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

[Bill Karwin]

Paul Bramscher wrote:

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.

My first reaction is that this is one of the many situations where it's
more trouble than it's worth to try to combine many data operations into
a single SQL query. People try to do that too often, and create very
expensive code; expensive for the programmer to develop and maintain,
and computationally expensive for MySQL to execute.

If you drop the requirement to do this in a single query containing six
outer joins, I predict it will execute a lot faster, even though it
takes multiple queries to execute it.

Instead, do six separate queries, each an inner join.
For instance:

SELECT ... FROM elements INNER JOIN resource USING (resource_id)
WHERE elements.resource_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN service USING (service_id)
WHERE elements.service_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN location USING (location_id)
WHERE elements.location_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN staff USING (staff_id)
WHERE elements.staff_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN subject USING (subject_id)
WHERE elements.subject_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN page USING (page_id)
WHERE elements.page_id IS NOT NULL;

You might also consider redesigning your database so that the keys go
the other direction:

CREATE TABLE element_to_page (
element_id INTEGER NOT NULL REFERENCES element,
page_id INTEGER NOT NULL REFERENCES page,
PRIMARY KEY (element_id, page_id),
);

Also create mapping tables for element_to_resource, element_to_service, etc.

You can use these mapping tables to create zero, one, or more references
between a given element and one of the other tables. Then you can take
those six foreign keys out of your elements table.

Regards,
Bill K.