Diana, Thanks for the suggestion. I completely forgot to add the index/primary key. Now it works just fine :) Thanks again.
In Peace, Saqib Ali "I fear, if I rebel against my Lord, the retribution of an Awful Day (The Day of Resurrection)" Al-Quran 6:15 http://docbook.sc-icc.org |--------+-----------------------> | | Diana Soares | | | <[EMAIL PROTECTED]| | | p.pt> | | | No Phone Info| | | Available | | | | | | 02/11/2003 | | | 08:59 AM | | | | |--------+-----------------------> >------------------------------------------------------------------------------------------------------------------------| | | | To: [EMAIL PROTECTED] | | cc: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> | | Subject: Re: optimizing left join query | >------------------------------------------------------------------------------------------------------------------------| Your user_info table is not indexed... (user_info.uid should have an index) Check the manual: http://www.mysql.com/doc/en/MySQL_indexes.html On Tue, 2003-02-11 at 16:16, [EMAIL PROTECTED] wrote: > Hello All, > > I have the following 2 tables and "LEFT join" query as follows: > > > mysql> describe user_lic; > +------------+------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +------------+------------+------+-----+---------+-------+ > | license_id | bigint(20) | | PRI | 0 | | > | user_id | bigint(20) | | PRI | 0 | | > | location | char(30) | YES | | NULL | | > +------------+------------+------+-----+---------+-------+ > > > mysql> describe user_info; > +----------+------------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+------------------+------+-----+---------+-------+ > | uid | int(10) unsigned | YES | | NULL | | > | location | char(30) | YES | | NULL | | > | mail | char(100) | YES | | NULL | | > +----------+------------------+------+-----+---------+-------+ > > query = SELECT * FROM user_lic LEFT JOIN user_info ON > user_lic.user_id=user_info.uid WHERE user_info.uid is NULL AND > (user_lic.location = 'Rochester, US') > > > When I run this query on a 'user_lic' table with 1000+ rows, it takes about > 20+ mins to complete the query. However a similar query on a Oracle DB > takes couple of minutes. > > I am using a high-end Sun Server, connected to a Xiotech SAN, using Fiber > Optics. I dont think the Hardware is the bottle neck. > > I am wondering how I can optimize the Query/MySQL DB to make this query go > faster. > > > > In Peace, > Saqib Ali > "I fear, if I rebel against my Lord, the retribution of an Awful Day (The > Day of Resurrection)" Al-Quran 6:15 > http://docbook.sc-icc.org > -- Diana Soares --------------------------------------------------------------------- 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
