Hi Afan, You should do an explain on both of your queries and check for the following parameters: 1. How many rows are being checked for your query? 2. What all indexes are being used? 3. What are the extra steps in query execution?
Otherwise IMHO, the first query with a union is any day better than the one with a join. Union queries are always easier for the optimizer to handle. Regards, Parvesh Garg On Wed, Jun 4, 2008 at 3:20 AM, afan pasalic <[EMAIL PROTECTED]> wrote: > Hi, > > I have two tables, "people" and "membership". Both have email column > (different column names though). > > CREATE TABLE `people` ( > `person_id` int(8) unsigned NOT NULL, > `address_id` int(8) default NULL, > `first_name` varchar(45) NOT NULL, > `last_name` varchar(45) NOT NULL, > `email` varchar(50) default NULL, > PRIMARY KEY (`person_id`), > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > > CREATE TABLE `membership` ( > `person_id` int(8) unsigned NOT NULL, > `organization_id` int(8) unsigned NOT NULL, > `email_address` varchar(45) default NULL, > `title` varchar(45) default NULL, > `department` varchar(45) default NULL, > `direct_phone` varchar(10) default NULL, > PRIMARY KEY (`person_id`,`organization_id`), > KEY `index_email` (`email_address`), > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 > > > I need to check if entered email address exists in either one of these > two tables. > I made these two queries. Which one is better one? > > ( > SELECT p.person_id, p.email, p.first_name, p.last_name > FROM people p > WHERE email='".$email."' > ) > UNION > ( > SELECT m.person_id, m.email_address as email, p.first_name, p.last_name > FROM membership m > LEFT JOIN people p ON p.person_id=m.person_id > WHERE m.email_address='".$email."' > ) > > or: > > SELECT p.person_id, p.email, p.first_name, p.last_name, m.email_address > FROM people p, membership m > WHERE (p.email='".$email."' OR m.email_address='".$email."') > AND p.person_id=m.person_id > > > > Thanks. > > -afan > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
