473,735 Members | 2,067 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

complex mysql 3.23 LEFT JOIN question

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.ed u
Jul 20 '05 #1
1 3455
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.resour ce_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN service USING (service_id)
WHERE elements.servic e_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN location USING (location_id)
WHERE elements.locati on_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.subjec t_id IS NOT NULL;
SELECT ... FROM elements INNER JOIN page USING (page_id)
WHERE elements.page_i d 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_reso urce, element_to_serv ice, 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.
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
5556
by: aaron | last post by:
I have a question about (i think) joining. If I have a table in a database that has this info: key - name - favorite 1 - john - 2 2 - judy - 3 3 - joe - 1 the favorite icecream table is this:
5
2240
by: Adam i Agnieszka Gasiorowski FNORD | last post by:
I need help width formulating the most effective (in terms of processing time) SQL query to count all the "new" documents in the repository, where "new" is defined as "from 00:00:01 up to 23:59:59 today". My current query does not give me satisfactory results, it creates a visible delay in rendering of the main page of one of the departments (Drugs) :8https://hyperreal.info > site, see for yourself, notice the delay
6
3073
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
4
1656
by: NotGiven | last post by:
I have two tables, Client and Project, related by clientID. I want to display a table with total CURRENT projects and total COMPLETED projects per client. I'd like for it to show like this: Client Name Current Proj Completed Proj Stan's Dormers 4 12 Larry Lincoln Dealership 2 1
0
2363
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not null default 0 auto_increment, a1 varchar(64) not null default '',
0
1384
by: Phil Powell | last post by:
I have a rather complicated query with a combination of LEFT JOINs and two MATCHES where the first match is non-boolean to get the accurate score, the second to search as boolean: SELECT image.id, image.image_name, (MATCH (image_name, image_alt, image_location_city, image_location_state, image_location_country) AGAINST ('test')
0
3944
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest version of this document can be found at: http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
1
1150
by: Sam | last post by:
For this example I have eliminated (most of) the unreferenced columns from the table definitions. My problem is: how does one formulate a select statement or create a view in SQL (I'm using MySQL (Ver 14.7 Distrib 4.1.16)) that implements the pseudo-code that follows the table definitions? BTW, I _have_ read the manual (several times). create table t2 ( # about 1K rows n2 smallint unsigned not null auto_increment unique key,
5
1568
by: Bob Bedford | last post by:
Hello all, I'm creating an invoice process for my website. I'm trying to show the invoice processing on a simple way. Let's explain how: I'm creating a table with the first column having the client's name. The second column has the article description. Now for every article, there is an invoice sent or to be sent (if the client
0
8964
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9253
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9201
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8202
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3277
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2740
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.