speeding up a search
I am trying to find records (from the 'stamps' table) that are NOT related to records in the 'links' table. To do this, I'm using a left join. For example: select s.*, l.item_id as lid from stamps as s left join links as l on (( s.item_id=l.item_id)) WHERE (s.sold is null); This worked great when there were just a few records in each table, but now that there are about 4000 records in each table, this simple query has slowed down to a crawl. It currently takes about 2 minutes to execute on a linux machine. Not good enough, especially since there will ultimately be at least 10,000 records in each table. Is there a better technique for doing this kind of search? Any suggestions much appreciated. Brgds: John -- Identry, LLC Northport, NY 11768 Ph: 631.754.8440 Fx: 631.980.4262 Em: [EMAIL PROTECTED] Member: ASDA, APS, ANA, Long Island Web Developers Guild Visit us at: www.identry.com Take control of your Web site -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up a search
Wow! One of the fields was a primary key, but the other wasn't. When I indexed the second field the performance went up from 2 minutes to about 2 seconds. Thanks a bunch. -- John Kelley Lingerfelt wrote: Make sure stamps.item_id and links.item_id are indexed, I had a similar problem, and when I indexed the columns, it went down to less than a half second. KL John Almberg wrote: I am trying to find records (from the 'stamps' table) that are NOT related to records in the 'links' table. To do this, I'm using a left join. For example: select s.*, l.item_id as lid from stamps as s left join links as l on (( s.item_id=l.item_id)) WHERE (s.sold is null); This worked great when there were just a few records in each table, but now that there are about 4000 records in each table, this simple query has slowed down to a crawl. It currently takes about 2 minutes to execute on a linux machine. Not good enough, especially since there will ultimately be at least 10,000 records in each table. Is there a better technique for doing this kind of search? Any suggestions much appreciated. Brgds: John -- Identry, LLC Northport, NY 11768 Ph: 631.754.8440 Fx: 631.980.4262 Em: [EMAIL PROTECTED] Member: ASDA, APS, ANA, Long Island Web Developers Guild Visit us at: www.identry.com Take control of your Web site -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Identry, LLC Northport, NY 11768 Ph: 631.754.8440 Fx: 631.980.4262 Em: [EMAIL PROTECTED] Member: ASDA, APS, ANA, Long Island Web Developers Guild Visit us at: www.identry.com Take control of your Web site
Re: speeding up a search
Run explain on the query and see if indexes are being used. John Almberg wrote: I am trying to find records (from the 'stamps' table) that are NOT related to records in the 'links' table. To do this, I'm using a left join. For example: select s.*, l.item_id as lid from stamps as s left join links as l on (( s.item_id=l.item_id)) WHERE (s.sold is null); This worked great when there were just a few records in each table, but now that there are about 4000 records in each table, this simple query has slowed down to a crawl. It currently takes about 2 minutes to execute on a linux machine. Not good enough, especially since there will ultimately be at least 10,000 records in each table. Is there a better technique for doing this kind of search? Any suggestions much appreciated. Brgds: John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up a search
Make sure stamps.item_id and links.item_id are indexed, I had a similar problem, and when I indexed the columns, it went down to less than a half second. KL John Almberg wrote: I am trying to find records (from the 'stamps' table) that are NOT related to records in the 'links' table. To do this, I'm using a left join. For example: select s.*, l.item_id as lid from stamps as s left join links as l on (( s.item_id=l.item_id)) WHERE (s.sold is null); This worked great when there were just a few records in each table, but now that there are about 4000 records in each table, this simple query has slowed down to a crawl. It currently takes about 2 minutes to execute on a linux machine. Not good enough, especially since there will ultimately be at least 10,000 records in each table. Is there a better technique for doing this kind of search? Any suggestions much appreciated. Brgds: John -- Identry, LLC Northport, NY 11768 Ph: 631.754.8440 Fx: 631.980.4262 Em: [EMAIL PROTECTED] Member: ASDA, APS, ANA, Long Island Web Developers Guild Visit us at: www.identry.com Take control of your Web site -- 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]
Re: speeding up a search
well, I have a query with 6 or 7 tables and some of them have more than 130,000 records, plus 2 inner joins and 3 left joins and a variable condition, but still is fast!!! BUT I limit the output to about 30 records ( to fit in a screen ). make sure to have index on the link key. - Original Message - From: John Almberg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 12:37 PM Subject: speeding up a search I am trying to find records (from the 'stamps' table) that are NOT related to records in the 'links' table. To do this, I'm using a left join. For example: select s.*, l.item_id as lid from stamps as s left join links as l on (( s.item_id=l.item_id)) WHERE (s.sold is null); This worked great when there were just a few records in each table, but now that there are about 4000 records in each table, this simple query has slowed down to a crawl. It currently takes about 2 minutes to execute on a linux machine. Not good enough, especially since there will ultimately be at least 10,000 records in each table. Is there a better technique for doing this kind of search? Any suggestions much appreciated. Brgds: John -- Identry, LLC Northport, NY 11768 Ph: 631.754.8440 Fx: 631.980.4262 Em: [EMAIL PROTECTED] Member: ASDA, APS, ANA, Long Island Web Developers Guild Visit us at: www.identry.com Take control of your Web site -- 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]
