First, may I suggest a few changes to your original queries:
select
u2.universityID as ID,
u2.urltype as type,
u4.username as user,
u3.itemID as ID2,
u1.ID as uID
from universityTBL as u1
join university_urlTBL as u2
on u2.universityID = u1.ID
join parsefileTBL as p1
on p1.university_urlID = u2.ID
join user_rolesTBL as u3
on u3.itemID = u2.ID
and u2.urltype = u3.itemType
join users as u4
on u3.userID = u4.user_id
where u2.urltype IN (1,2)
and u3.process = 20
and u1.ID IN (40,102,257,267,379,394);
Changes:
moved a conditional term linking u2 and u3 into the ON clause of
the u3 JOIN
Changed your OR lists into IN (,,)
Removed quotes from around your numbers (They are not strings,
don't quote them)
select
u1.universityID as ID
, u1.urltype as type
, p1.start_status as status
FROM parsefileTBL as p1
INNER JOIN latestParseStatusTBL as l1
on p1.fileID = l1.itemID
LEFT JOIN university_urlTBL as u1
on p1.university_urlID = u1.ID
WHERE u1.universityID IN (40,102,257,267,379,394,460,541,560)
Changes:
Moved your outer join to the end of your join list and converted
the RIGHT join to a LEFT join.
Changed your OR list into an IN(,,,)
Unquoted the numbers
I question the logic of this last query. Because of the LEFT join, the
WHERE condition is not applied until after the Cartesian product of ("p1"
IJ "l1") LJ "u1" is built as a virtual table. You eliminate all
non-matching rows from u1 by looking for a non-null value in the results.
This query should move faster written as
select
u1.universityID as ID
, u1.urltype as type
, p1.start_status as status
FROM parsefileTBL as p1
INNER JOIN latestParseStatusTBL as l1
on p1.fileID = l1.itemID
INNER JOIN university_urlTBL as u1
on p1.university_urlID = u1.ID
and u1.universityID IN (40,102,257,267,379,394,460,541,560)
Now, to address the JOIN of the two queries so that the results of query 1
are optionally matched with the rows of query 2
select
u1.universityID as ID
, u1.urltype as type
, p1.start_status as status
, u4.username as user
, u3.itemID as ID2
, u1.ID as uID
FROM university_urlTBL as u1
INNER JOIN parsefileTBL as p1
on p1.university_urlID = u1.ID
INNER JOIN latestParseStatusTBL as l1
on p1.fileID = l1.itemID
LEFT join user_rolesTBL as u3
on u3.itemID = u2.ID
and u2.urltype IN (1,2)
and u2.urltype = u3.itemType
and u3.process = 20
LEFT join users as u4
on u3.userID = u4.user_id
WHERE u1.universityID IN (40,102,257,267,379,394,460,541,560)
ORDER BY 1,2,3
Notes:
1) You already had the first two columns the same for each query so I kept
those tables joined the same way. The 3rd and 4th columns were optional
data so the tables they source from are LEFT JOINed
2) The ON condition of the LEFT JOIN of user_rolesTBL has two conditions
(u2.urltype... and u3.process...) that you may have considered putting
into the WHERE clause. They belong in the ON clause of the join because
those are two of the conditions by which we decide which rows are joined,
not which rows do we return as results of the query.
3) I added an order by clause so that the report will format as you
suggested (listed by ID, type, status)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"bruce" <[EMAIL PROTECTED]> wrote on 09/29/2004 10:05:49 AM:
> hey shawn!!!
>
> got your msg about helping speed up the mysql!!! thanks. i got to
thinking
> that if the sql/interactions where the issue, why not rewrite the app to
> reduce the number of round trips to hit the db for a given page to be
> displayed..
>
> the following sql statements are used to produce the two tables:
>
------8<--snip--8<-------
> sql
> t1:
> select
> u2.universityID as ID,
> u2.urltype as type,
> u4.username as user,
> u3.itemID as ID2,
> u1.ID as uID
> from universityTBL as u1
> join university_urlTBL as u2
> on u2.universityID = u1.ID
> join parsefileTBL as p1
> on p1.university_urlID = u2.ID
> join user_rolesTBL as u3
> on u3.itemID = u2.ID
> join users as u4
> on u3.userID = u4.user_id
> where u2.urltype = u3.itemType
> and (u2.urltype = '1' or u2.urltype='3')
> and u3.process = '20'
> and (u1.ID='40' or u1.ID='102' or u1.ID='257' or u1.ID='267'
> or u1.ID='379' or u1.ID='394');
>
> t2:
> select u1.universityID as ID, u1.urltype as type, p1.start_status as
status
> from university_urlTBL as u1 right join parsefileTBL as p1 on u1.ID =
> p1.university_urlID join latestParseStatusTBL as l1 on p1.fileID =
l1.itemID
> where u1.universityID='40' or u1.universityID='102' or
u1.universityID='257'
> or u1.universityID='267' or u1.universityID='379' or
u1.universityID='394'
> or u1.universityID='460' or u1.universityID='541' or
> u1.universityID='560'....
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 29, 2004 6:42 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: merging of two tables using temp tables???
>
>
>
> If you post your two "original" SQL statements (the ones you use to
build
> your example tables) I think I can help you to merge your results,
possibly
> without the need for temporary tables. Also, what version of MySQL are
you
> using?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
> "bruce" <[EMAIL PROTECTED]> wrote on 09/29/2004 12:57:34 AM:
>
> > hi....
> >
> > i have a situation where i create the following tables via two
different
> > select sql statements.
> >
> > +-----+------+-------+------+-----+
> > | ID | type | user | ID | uID |
> > +-----+------+-------+------+-----+
> > | 40 | 1 | admin | 157 | 40 |
> > | 102 | 1 | admin | 405 | 102 |
> > | 257 | 1 | admin | 1025 | 257 |
> > | 267 | 1 | admin | 1065 | 267 |
> > | 379 | 1 | admin | 1513 | 379 |
> > +-----+------+-------+------+-----+
> > 5 rows in set (0.00 sec)
> >
> > +------+------+--------+
> > | ID | type | status |
> > +------+------+--------+
> > | 40 | 1 | 0 |
> > | 40 | 2 | 0 |
> > | 40 | 3 | 0 |
> > | 40 | 4 | 0 |
> > | 102 | 1 | 0 |
> > | 102 | 2 | 0 |
> > | 102 | 3 | 0 |
> > | 102 | 4 | 0 |
> > | 257 | 1 | 0 |
> > | 257 | 2 | 0 |
> > | 257 | 3 | 0 |
> > | 257 | 4 | 0 |
> > | 267 | 1 | 0 |
> > | 267 | 2 | 0 |
> > | 267 | 3 | 0 |
> > | 267 | 4 | 0 |
> > | 379 | 1 | 0 |
> > | 379 | 2 | 0 |
> > | 379 | 3 | 0 |
> > | 379 | 4 | 0 |
> > | 394 | 1 | 0 |
> > | 394 | 2 | 0 |
> > | 394 | 3 | 0 |
> > | 394 | 4 | 0 |
> > | 460 | 1 | 0 |
> > | 460 | 2 | 0 |
> > | 460 | 3 | 0 |
> > | 460 | 4 | 0 |
> > | 541 | 1 | 0 |
> > | 541 | 2 | 0 |
> > | 541 | 3 | 0 |
> > | 541 | 4 | 0 |
> >
> >
> > i'd like to be able to merge/combine the two tables so that i get
> > +------+------+--------+------+-------+-----+
> > | ID | type | status | user | ID | uID |
> > +------+------+--------+------+-------+-----+
> > | 40 | 1 | 0 |
> > | 40 | 2 | 0 | .
> > | 40 | 3 | 0 | .
> > | 40 | 4 | 0 |
> > | 102 | 1 | 0 |
> > | 102 | 2 | 0 |
> > | 102 | 3 | 0 |
> > | 102 | 4 | 0 |
> >
> > with the appropriate information in the various columns/rows...
> >
> > i'm looking to be able to fill the resulting table with the
information if
> > it's present, or to have nulls/'0' where the information isn't
> available...
> >
----8<--snip---8<------
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
