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: tbl t1: +-----+------+-------+------+-----+ | 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) tbl t2: +------+------+--------+ | 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 | 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... > > i'd prefer to do this in mysql if possible, as my gut tells me the operation > would be faster/more efficient in mysql, than if i coded this in php/perl... > > i believe that i's need to create a temp table based on each select, and > then some how merge the two temp tables, and finally do a select on the > resulting table to get the values i need... > > looking through google/mysql hasn't shed any light on this one... > > any ideas/thoughts/comments on how i can do this..... > > thanks... > > -bruce > > > ps... > > the actual select sql used to create the 2 tbls are listed: > select > u4.username as user, > u3.itemID as ID, > u1.ID as uID > from universityTBL as u1 > left join university_urlTBL as u2 > on u2.universityID = u1.ID > right join parsefileTBL as p1 > on p1.university_urlID = u2.ID > left join user_rolesTBL as u3 > on u3.itemID = u2.ID > left join users as u4 > on u3.userID = u4.user_id > where u2.urltype = u3.itemType > and u2.urltype = '1' > 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'); > > 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' > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
