CODEWITHSUNDEEP
mysqlselectjoin
Sandy
Sandy

Reputation: 63

MYSQL - JOIN with OR condition

I have 3 Tables CompanyMaster(Which has 3 Million rows), Token1, Token2 and the table structure is,

CompanyMaster

CREATE TABLE `CompanyMaster` (
  `CompanyUID` int(11) NOT NULL AUTO_INCREMENT,
  `WebDomain` varchar(150) DEFAULT NULL,
  `CompanyPrimaryName` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`CompanyUID`)  
) ENGINE=InnoDB AUTO_INCREMENT=3941244 DEFAULT CHARSET=latin1

Token1

CREATE TABLE `Token1`(
  `CompanyUID` int(11) NOT NULL,
  `Token` varchar(50) NOT NULL,
  KEY `Token` (`Token`),
  KEY `CompanyUID` (`CompanyUID`),
  CONSTRAINT `CompanyAlias4_ibfk_1` FOREIGN KEY (`CompanyUID`) REFERENCES `CompanyMaster` (`CompanyUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Token2

CREATE TABLE `Token2` (
  `CompanyUID` int(11) NOT NULL,
  `Token` varchar(100) NOT NULL,
  KEY `Token` (`Token`),
  KEY `CompanyUID` (`CompanyUID`),
  CONSTRAINT `CompanyAlias5_ibfk_1` FOREIGN KEY (`CompanyUID`) REFERENCES `CompanyMaster` (`CompanyUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

I want to get the WebDomain from the CompanyMaster table using the Token1 and Token2 tables.

The Query i am using is,

SELECT WebDomain FROM CompanyMaster WHERE CompanyUID IN (
SELECT CompanyUID FROM Token1 WHERE Token='appleinc'
UNION
SELECT CompanyUID FROM Token2 WHERE Token='d012233:q122100:')

This query takes almost 30 Seconds to get the result. I executed the sub-query alone, which is taking < 100 milli-seconds.So the problem is with the IN condition.

I replaced the query with join and it is executing in < 200 ms,

SELECT c.CompanyUID FROM `CompanyMaster` c
JOIN `Token1` tk1
ON tk1.CompanyUID = c.CompanyUID AND tk1.Token= 'appleinc'
JOIN `Token2` tk2
ON tk2.CompanyUID = c.CompanyUID AND tk2.Token= 'd012233:q122100:'

But the problem with above query is , if tk1.Alias = 'appleinc' or tk2.Alias = 'd012233:q122100:' fails it is giving output as empty row. But i want the matched rows even if only one condition is matched.

Please help me how to solve this one ? And i also want the query to be executed in less than 10 milli-seconds. Is it achievable ?

Upvotes: 1

Views: 3363

Answers (2)

Prasana Alawekar
Prasana Alawekar

Reputation: 1

You can use where clause to filter your record on the basis of toke1 and token2. On the basis of your requirement you can change that clause.

Please check following SQL. Hope it will solve your problem.

SELECT c.CompanyUID, c.WebDomain FROM CompanyMaster c LEFT JOIN Token1 tk1 ON tk1.CompanyUID = c.CompanyUID LEFT JOIN Token2 tk2 ON tk2.CompanyUID = c.CompanyUID WHERE tk1.Token = '123' OR tk2.Token = 'xyz';

Upvotes: 0

trincot
trincot

Reputation: 349946

You should certainly get better performance with UNION ALL than with UNION, as it will have no difference for your case in output, but it does not need to filter out duplicates like UNION does:

SELECT     WebDomain 
FROM       CompanyMaster
WHERE      CompanyUID IN
           ( SELECT CompanyUID 
             FROM   Token1
             WHERE  Token = 'appleinc'
             UNION ALL
             SELECT CompanyUID 
             FROM   Token2
             WHERE  Token = 'd012233:q122100:')

However, if you would put the UNION in the outer query, it might even give better performance, like this:

SELECT     WebDomain 
FROM       CompanyMaster m
INNER JOIN Token1 t ON t.CompanyUID = m.CompanyUID
WHERE      Token = 'appleinc'
UNION
SELECT     WebDomain 
FROM       CompanyMaster m
INNER JOIN Token2 t ON t.CompanyUID = m.CompanyUID
WHERE      Token = 'd012233:q122100:'

Here it is probably important to only get unique values, so you need UNION without ALL here.

Upvotes: 1

Related Questions