MySQL query slow with OR in WHERE statement

Question

I have a SQL query which looks simple but runs very slow ~4s:

SELECT tblbooks.*
FROM tblbooks LEFT JOIN
    tblauthorships ON tblbooks.book_id = tblauthorships.book_id
WHERE (tblbooks.added_by=3 OR tblauthorships.author_id=3)
GROUP BY tblbooks.book_id
ORDER BY tblbooks.book_id DESC
LIMIT 10

EXPLAIN result:

| id   | select_type | table          | type  | possible_keys     | key     | key_len | ref                    | rows | Extra       |
+------+-------------+----------------+-------+-------------------+---------+---------+------------------------+------+-------------+
|    1 | SIMPLE      | tblbooks       | index | fk_books__users_1 | PRIMARY | 62      | NULL                   |   10 | Using where |
|    1 | SIMPLE      | tblauthorships | ref   | book_id           | book_id | 62      | tblbooks.book_id       |    1 | Using where |
+------+-------------+----------------+-------+-------------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.000 sec)

If I run the above query individually on each part of OR in WHERE statement, both queries return result in less than 0.01s.

Simplified schema:

tblbooks (~1 million rows):

| Field         | Type                  | Null | Key | Default             | Extra          |
+---------------+-----------------------+------+-----+---------------------+----------------+
| id            | int(10) unsigned      | NO   | MUL | NULL                | auto_increment |
| book_id       | varchar(20)           | NO   | PRI | NULL                |                |
| added_by      | int(11) unsigned      | NO   | MUL | NULL                |                |
+---------------+-----------------------+------+-----+---------------------+----------------+

tblauthorships (< 100 rows):

| Field         | Type             | Null | Key | Default             | Extra          |
+---------------+------------------+------+-----+---------------------+----------------+
| authorship_id | int(11) unsigned | NO   | PRI | NULL                | auto_increment |
| book_id       | varchar(20)      | NO   | MUL | NULL                |                |
| author_id     | int(11) unsigned | NO   | MUL | NULL                |                |
+---------------+------------------+------+-----+---------------------+----------------+

Both book_id and author_id columns in tblauthorships have their index created.

Can anyone point me to the right direction?

Note: I'm aware of book_id varchar issue.

Bill_Karwin_20860 · Answer

My usual analogy for indexing is a telephone book. It's sorted by last name then by first name. If you look up a person by last name, you can find them efficiently. If you look up a person by last name AND first name, it's also efficient. But if you look up a person by first name only, the sort order of the book doesn't help, and you have to search every page the hard way.

Now what happens if you need to search a telephone book for a person by last name OR first name?

SELECT * FROM TelephoneBook WHERE last_name = 'Thomas' OR first_name = 'Thomas';

This is just as bad as searching only by first name. Since all entries matching the first name you searched should be included in the result, you have to find them all.

Conclusion: Using OR in an SQL search is hard to optimize, given that MySQL can use only one index per table in a given query.

Solution: Use two queries and UNION them:

SELECT * FROM TelephoneBook WHERE last_name = 'Thomas'
UNION
SELECT * FROM TelephoneBook WHERE first_name = 'Thomas';

The two individual queries each use an index on the respective column, then the results of both queries are unified (by default UNION eliminates duplicates).

In your case you don't even need to do the join for one of the queries:

(SELECT b.*
 FROM tblbooks AS b
 WHERE b.added_by=3)
UNION
(SELECT b.*
 FROM tblbooks AS b
 INNER JOIN tblauthorships AS a USING (book_id)
 WHERE a.author_id=3)
ORDER BY book_id DESC
LIMIT 10

user_1766831 · Answer

The two answers so far are not very optimal. Since they have both UNION and LIMIT, let me further optimize their answers:

( SELECT ...
    ORDER BY ...
    LIMIT 10
) UNION DISTINCT
( SELECT ...
    ORDER BY ...
    LIMIT 10
)
ORDER BY ...
LIMIT 10

This gives each SELECT a chance to optimize the ORDER BY and LIMIT, making them faster. Then the UNION DISTINCT dedups. Finally, the first 10 are peeled off to make the resultset.

If there will be pagination via OFFSET, this optimization gets trickier. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or

Also... Your table needs two indexes:

INDEX(added_by)
INDEX(author_id)

(Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.)

MySQL query slow with OR in WHERE statement

2 Answers