CODEWITHSUNDEEP
mysqlsql
HTMHell
HTMHell

Reputation: 6016

MySQL performs a slow query with a boolean condition

Example of the table:

       id       |      source     | removed
17D30437329A9B9 |                 |   0
M851X0LG81045F  | 17D30437329A9B9 |   0
QQG1RU1M8E5JHO  |                 |   0
QDVHFNFKF0Z80W  | 17D30437329A9B9 |   0
8BEFSFGUPBXJHV  |                 |   0

When I query:

SELECT  *
FROM    `uploads`
WHERE   (id = '17D30437329A9B9 ' OR `source` = '17D30437329A9B9 ')
AND     removed = 0

The query takes ~25 seconds (I have ~17 million rows).

But when I run this:

SELECT  *
FROM    `uploads`
WHERE   (id = '17D30437329A9B9 ' OR `source` = '17D30437329A9B9 ')

Or this:

SELECT  *
FROM    `uploads`
WHERE   (`id` = '17D30437329A9B9 ')
AND     removed = 0

Or that:

SELECT  *
FROM    `uploads`
WHERE   (`source` = '17D30437329A9B9 ')
AND     removed = 0

The queries runs fast.

Why is the 1st query runs really slow, and how can I fix it?

EDIT: The result from EXPLAIN SELECT * FROM uploads WHERE (id = '17D30437329A9B9 ' OR source = '17D30437329A9B9 ') AND removed = 0;:

+----+-------------+---------+------+-------------------------------------------------------+---------+---------+-------+---------+-------------+
| id | select_type | table   | type | possible_keys                                         | key     | key_len | ref   | rows    | Extra       |
+----+-------------+---------+------+-------------------------------------------------------+---------+---------+-------+---------+-------------+
|  1 | SIMPLE      | uploads | ref  | PRIMARY,removed,source,idx_member_selectFiles,id,id_2 | removed | 1       | const | 8829521 | Using where |
+----+-------------+---------+------+-------------------------------------------------------+---------+---------+-------+---------+-------------+

Upvotes: 1

Views: 1069

Answers (3)

spencer7593
spencer7593

Reputation: 108430

The MySQL execution plans for queries with OR conditions are sometimes not optimal.

I suggest you re-write the query to combine results from two separate queries.

SELECT u1.*
  FROM `uploads` u1
 WHERE u1.id      = '17D30437329A9B9 '
   AND u1.removed = 0

 UNION ALL

SELECT u2.*
  FROM `uploads` u2
 WHERE u2.source  = '17D30437329A9B9 '
   AND u2.removed = 0
   AND NOT (u2.id <=> '17D30437329A9B9 ')

Each SELECT can make effective use of the most appropriate index.

The first SELECT can use an index with leading column of id. The second SELECT can use an index with leading column of source.

FOLLOWUP

Q: And if i'm using IN? like WHERE (id IN ('a','b') OR source IN ('a','b')) AND removed = 0

A: I'd use the same approach.

SELECT u1.*
  FROM `uploads` u1
 WHERE u1.id     IN ('17D30437329A9B9 ', ... )
   AND u1.removed = 0

 UNION ALL

SELECT u2.*
  FROM `uploads` u2
 WHERE u2.source IN ('17D30437329A9B9 ', ... )
   AND u2.removed = 0
   AND ( u2.id IS NULL OR u2.id NOT IN ('17D30437329A9B9 ', ... ) )

The last condition on the second SELECT is intended to prevent the query from returning a row that was already returned by the first SELECT.

With the NOT IN, just be sure that none of the values in the list are NULL. (If the NOT IN list includes a NULL value, then no rows will satisfy the condition.)

If the id column in uploads is guaranteed to be NOT NULL, the check for NULL can be eliminated. (We don't see any table definition, so we can't tell whether id is defined as NOT NULL, so write the query to work in the more general case, rather than based on a possibly wrong assumption.)

Upvotes: 0

Fabian Barney
Fabian Barney

Reputation: 14549

B-Tree indexes are bad for columns with low cardinality. And in your case MySQL chooses the index for column removed what is VERY bad, because there only exist two distinct values.

I suspect to never see a benefit of the index on removed. Delete the index on removed.

A Bitmap-Index instead of a B-Tree Index would be good. As far as I know MySQL does not support Bitmap-Indexes.

Furthermore an index on (id, source) would help in this case.

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

it seem that there is no composite index.

run this and try again

Create composite INDEX

ALTER TABLE `uploads`
ADD KEY (`id`,`source`,`removed`);

please let me know if it works.

Upvotes: 1

Related Questions