MYSQL - Select query with multiple OR conditions is Slow

Question

I have a table of over 2 million rows and i need to rapidly do select queries on it in a loop.

SELECT ID,WebSite FROM `CompanyData` WHERE A1='data1' OR A2='data2' OR A3='data3'

It's taking 300 milli-seconds. I feel it shouldn't take this much time. Here is the EXPLAIN from that query:

--------+---------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table    | type  | possible_keys  | key      |  key_len    | ref  | rows  | Extra                              |
+----+-------------+----------+-------+---------------+------+---------+------+---------+-------------------------------------------+
|  1 | SIMPLE     | CompanyData | index | A1,A2,A3     | A1,A2,A3 | 153,153,153 | NULL | 3     | Using union(A1,A2,A3); Using where |
+----+-------------+----------+-------+---------------+------+---------+------+---------+-------------------------------------------+

Here is the table structure:

CREATE TABLE `CompanyData` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `WebSite` varchar(150) DEFAULT NULL,
  `CompanyName` varchar(200) DEFAULT NULL,
  `A1` varchar(150) DEFAULT NULL,
  `A2` varchar(150) DEFAULT NULL,
  `A3` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `WebSite` (`WebSite`,`CompanyName`),
  KEY `CompanyName` (`CompanyName`),
  KEY `A1` (`A1`),
  KEY `A2` (`A2`),
  KEY `A3` (`A3`)
) ENGINE=InnoDB AUTO_INCREMENT=3931223 DEFAULT CHARSET=latin1

Recently i have inserted another 10 million records to the table. Then the same Query is taking around 3 Seconds.

Please suggest a way to improve the Select query lookup. Even I am ready to restructure the table.

Thanks

The general suggestion is to change the query and use union instead of series of ORs. However, the explain seems to suggest that mysql optimised your query already from this aspect, but it does not hurt to try it out. — Shadow
– Shadow, Commented Mar 1, 2016 at 13:23
add a composite key: KEY AX (A1,A2,A3). Mysql can only use one key. — Bernd Buffen
– Bernd Buffen, Commented Mar 1, 2016 at 13:23
That is not going to help! Mysql would not be able to use the composite key to satisfy the OR A2='data2' OR A3='data3' criteria because each criterion relates to a single field only and a composite key can only be used for looking up a single field if that single field is the leftmost one in the index. — Shadow
– Shadow, Commented Mar 1, 2016 at 13:26
No one ever needs to do queries 'in a loop'. I think the problem's right there! — Strawberry
– Strawberry, Commented Mar 1, 2016 at 13:29

Gordon Linoff · Accepted Answer · 2016-03-01 13:27:04Z

6

It is difficult for MySQL to optimize conditions with an OR. You might be better trying:

SELECT ID, WebSite
FROM `CompanyData`
WHERE A1 = 'data1'
UNION
SELECT ID, WebSite
FROM `CompanyData`
WHERE A2 = 'data2' 
UNION
SELECT ID, WebSite
FROM `CompanyData`
WHERE A3 = 'data3';

Note that this uses UNION instead of UNION ALL. An alternative version with UNION ALL looks like this (assuming the values are never NULL):

SELECT ID, WebSite
FROM `CompanyData`
WHERE A1 = 'data1'
UNION ALL
SELECT ID, WebSite
FROM `CompanyData`
WHERE A2 = 'data2' AND A1 <> 'data1'
UNION ALL
SELECT ID, WebSite
FROM `CompanyData`
WHERE A3 = 'data3' AND A1 <> 'data1' AND A2 <> 'data2';

For this query, the best indexes are composite indexes: (A1, ID, WebSite), (A2, A1, ID, Website), and (A3, A1, A2, ID, WebSite).

answered Mar 1, 2016 at 13:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2016-03-02 02:55:23Z

0

Arrays splayed across columns are problematical.

Give this a try...

CREATE TABLE `CompanyData` (
    `ID` int(11) NOT NULL AUTO_INCREMENT, 
    `WebSite` varchar(150) DEFAULT NULL, 
    `CompanyName` varchar(200) DEFAULT NULL, 
    PRIMARY KEY (`ID`), 
    UNIQUE KEY `WebSite` (`WebSite`,`CompanyName`), 
    KEY `CompanyName` (`CompanyName`), 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `Stuff` (
    `CompanyID` int(11) NOT NULL
    `A` varchar(150) NOT NULL, 
    PRIMARY KEY (A, CompanyID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT  c.ID, c.WebSite
    FROM  CompanyData AS c
    JOIN  Stuff AS s ON c.ID = s.CompanyID
    WHERE  s.A IN ('data1', 'data2', 'data3');

answered Mar 2, 2016 at 2:55

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

MYSQL - Select query with multiple OR conditions is Slow

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related