Left Join problem - Please Help
Whenever I try to issue a query which has a left join for two tables and a where clause for a column in the second table that is a varchar the query runs indefinitely. If I change the where clause to search on a numeric type column the query runs and returns expected values. Does anyone know what I am doing wrong? This is my first time using Mysql, so if this isn't the right list to post on, please tell me where I can post beginner mysql questions. Thanks for your help in advance. Anya Miretsky Computer Technology Dept. Brooklyn Botanic Garden 1000 Washington Avenue Brooklyn, NY 11225 (718)623-7265 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Left Join problem - Please Help
Show us your table structure, some table values and the SELECT statement that works and the one that doesn't -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 12:13 PM To: '[EMAIL PROTECTED]' Subject: Left Join problem - Please Help Whenever I try to issue a query which has a left join for two tables and a where clause for a column in the second table that is a varchar the query runs indefinitely. If I change the where clause to search on a numeric type column the query runs and returns expected values. Does anyone know what I am doing wrong? This is my first time using Mysql, so if this isn't the right list to post on, please tell me where I can post beginner mysql questions. Thanks for your help in advance. Anya Miretsky Computer Technology Dept. Brooklyn Botanic Garden 1000 Washington Avenue Brooklyn, NY 11225 (718)623-7265 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Left Join problem - Please Help
I don't think it's hanging. I think it's going through the process of determining joint criteria for 11,900 table entries multiplied by 88,000 other table entries, then doing a text search on the result, which is much slower than an integer search. I'm posting this back to the mysql list to see what the experts think. -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 2:17 PM To: 'Rick Emery' Subject: RE: Left Join problem - Please Help Biblio has 11,901 and keyword has 87,971, also I indexed the keyword column in the keyword table. Is the fact that I have the same name for a column as for the table possibly a problem? -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 3:17 PM To: 'Miretsky, Anya' Subject: RE: Left Join problem - Please Help How many records are in these tables? -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 1:51 PM To: 'Rick Emery' Subject: RE: Left Join problem - Please Help Table structure would be: Biblio table with the following columns: p_biblio int unsigned not null primary key, authors varchar(255), title varchar(255) , citation varchar(255) Keyword table with the following columns: p_keyword int unsigned not null primary key, fk_biblio int unsigned not null, Keyword varchar(255) Sql statement that works: Select distinct biblio.* from biblio left join keyword on biblio.p_biblio=keyword.fk_biblio where fk_biblio3; Sql statement that doesn't work - msql hangs executing this: Select distinct biblio.* from biblio left join keyword on biblio.p_biblio=keyword.fk_biblio where keyword=SOMESTRING; -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 2:10 PM To: 'Miretsky, Anya'; '[EMAIL PROTECTED]' Subject: RE: Left Join problem - Please Help Show us your table structure, some table values and the SELECT statement that works and the one that doesn't -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 12:13 PM To: '[EMAIL PROTECTED]' Subject: Left Join problem - Please Help Whenever I try to issue a query which has a left join for two tables and a where clause for a column in the second table that is a varchar the query runs indefinitely. If I change the where clause to search on a numeric type column the query runs and returns expected values. Does anyone know what I am doing wrong? This is my first time using Mysql, so if this isn't the right list to post on, please tell me where I can post beginner mysql questions. Thanks for your help in advance. Anya Miretsky Computer Technology Dept. Brooklyn Botanic Garden 1000 Washington Avenue Brooklyn, NY 11225 (718)623-7265 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Left Join problem - Please Help
possibly -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 2:32 PM To: 'Rick Emery' Subject: RE: Left Join problem - Please Help When I do this without specifying a left join with just :select biblio.* from biblio,keyword where p_biblio=fk_biblio and keyword=somestring; it works fairly fast, is it the left join that's making it so slow? -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 3:29 PM To: 'Miretsky, Anya' Cc: '[EMAIL PROTECTED]' Subject: RE: Left Join problem - Please Help I don't think it's hanging. I think it's going through the process of determining joint criteria for 11,900 table entries multiplied by 88,000 other table entries, then doing a text search on the result, which is much slower than an integer search. I'm posting this back to the mysql list to see what the experts think. -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 2:17 PM To: 'Rick Emery' Subject: RE: Left Join problem - Please Help Biblio has 11,901 and keyword has 87,971, also I indexed the keyword column in the keyword table. Is the fact that I have the same name for a column as for the table possibly a problem? -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 3:17 PM To: 'Miretsky, Anya' Subject: RE: Left Join problem - Please Help How many records are in these tables? -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 1:51 PM To: 'Rick Emery' Subject: RE: Left Join problem - Please Help Table structure would be: Biblio table with the following columns: p_biblio int unsigned not null primary key, authors varchar(255), title varchar(255) , citation varchar(255) Keyword table with the following columns: p_keyword int unsigned not null primary key, fk_biblio int unsigned not null, Keyword varchar(255) Sql statement that works: Select distinct biblio.* from biblio left join keyword on biblio.p_biblio=keyword.fk_biblio where fk_biblio3; Sql statement that doesn't work - msql hangs executing this: Select distinct biblio.* from biblio left join keyword on biblio.p_biblio=keyword.fk_biblio where keyword=SOMESTRING; -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 2:10 PM To: 'Miretsky, Anya'; '[EMAIL PROTECTED]' Subject: RE: Left Join problem - Please Help Show us your table structure, some table values and the SELECT statement that works and the one that doesn't -Original Message- From: Miretsky, Anya [mailto:[EMAIL PROTECTED]] Sent: Friday, February 15, 2002 12:13 PM To: '[EMAIL PROTECTED]' Subject: Left Join problem - Please Help Whenever I try to issue a query which has a left join for two tables and a where clause for a column in the second table that is a varchar the query runs indefinitely. If I change the where clause to search on a numeric type column the query runs and returns expected values. Does anyone know what I am doing wrong? This is my first time using Mysql, so if this isn't the right list to post on, please tell me where I can post beginner mysql questions. Thanks for your help in advance. Anya Miretsky Computer Technology Dept. Brooklyn Botanic Garden 1000 Washington Avenue Brooklyn, NY 11225 (718)623-7265 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Left Join problem - Please Help
* Miretsky, Anya Select distinct biblio.* from biblio left join keyword on biblio.p_biblio=keyword.fk_biblio where keyword=SOMESTRING; Try swapping the tables: Select distinct biblio.* from keyword left join biblio on biblio.p_biblio=keyword.fk_biblio where keyword=SOMESTRING; The left join is used when there may exist rows in the left table which may not have a related row in the right table, but you still want to include the fields from the left table. The fields from the right table are all NULL in the result row in this case. In this case you probably don't need the left join: Select distinct biblio.* from keyword, biblio where biblio.p_biblio=keyword.fk_biblio and keyword=SOMESTRING; The above statements does not select anything from the keyword table, but the where clause use the keyword table only, not the biblio table... In your case you read all the biblio rows (11,901), and for each of them you do a lookup on the related keywords matching SOMESTRING... this is rarely what you want, the exception beeing when there are very very many keywords and very few biblio rows. It is faster to use the index for all mathing keywords, and then do the lookup to find the related biblio row. By simply swapping the table names, you select FROM the keyword table, and then join with the matching biblio rows. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
