OR clauses drastically slowing down MYSQL query

Question

I have a query that is slowed down because of the following 3 OR clauses:

select * from book join library_member USING(book_id)
where member_id = 1145
and
( Date_add(Current_date(), interval 1 month) BETWEEN 
                 book.check_in AND book.check_out ) 
          OR ( Current_date() BETWEEN book.check_in AND 
             book.check_out ) 
          OR ( book.check_out BETWEEN Current_date() AND Date_add( 
                                              Current_date(), 
                                              interval 1 month) 
             ) 
          OR ( book.check_in BETWEEN 
               Current_date() AND Date_add(Current_date(), interval 1 month) )

Is there a better way of querying for either of these 4 scenarios?

Please prepend each column with the table name or alias where it belongs to. It's not obvious to us which tables have which columns. — The Impaler, Jul 22 '20 at 01:52
Hard to believe. How many books does member 1145 have checked out? Are you sure you have the correct conditions? How can a book checkout or checkin be a month in the future? What are the table definitions? What indexes do you have? What does EXPLAIN say about the execution plan? — user207421, Jul 22 '20 at 02:13

score 0 · Answer 1 · answered Jul 22 '20 at 05:25

0

Consider the following:

SELECT c.olumns
     , y.ou
     , a.ctually
     , n.eed
  FROM book b
  JOIN library_member m
    ON m.book_id = b.book_id
 WHERE m.member_id = 1145
   AND DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH) > b.check_in
   AND CURRENT_DATE() < b.check_out

Indexes on (b.check_in,b.check_out) and (m.book_id) would be useful

answered Jul 22 '20 at 05:25

Strawberry

33,750
13
40
57

This gives a different result from the query I had originally :( – street_jesus Jul 22 '20 at 15:41
It can only be a case of rearranging the `less thans` and `greater thans` - or possibly using an exclusion join. See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) – Strawberry Jul 22 '20 at 15:49
This solution won’t work. Here the scenario that it is missing. Dates 1 Jan 2020 Jun 2020 Dates 2 Jan 2019 Jun 2021 It doesn’t handle this scenario as this will return false: AND Jun 2021 > Jan 2020 AND Jan 2019 < Jun 2020 – yg-dba Jul 23 '20 at 12:27

score -1 · Answer 2 · answered Jul 22 '20 at 02:24

-1

Yes - there is a faster way to find overlaps of dates! I recently had a requirement like this and I was delighted to find a solution on stack overflow that doesn't use OR.

I tested this out in my code and it works flawlessly and performs really fast.

You can check for overlaps of dates like this:

AND DATEDIFF(LEAST(Date_add(Current_date(), interval 1 month), book.check_out),GREATEST(Current_date(), book.check_in)) > 0;

https://stackoverflow.com/a/62761904/11822079

answered Jul 22 '20 at 02:24

yg-dba

330
1
6

That is correct that functions do not use indexes. However, based on a lot of experience with this, this solution works much faster since it's a single WHERE clause instead of 4. You do need to add additional clauses based on other fields. In the query in the question, there should be an index on member_id – yg-dba Jul 22 '20 at 12:31
two where conditions without an OR would be faster. You are correct on that. However, your answer to this question wont work as it's missing a scenario. That's why I love this solution of one clause for the overlap dates. It works much more efficiently than the ORs, and it gives the same result. – yg-dba Jul 22 '20 at 22:42
The test for overlaps is when event A starts before event B ends, and ends after Event B starts. There's no other test. – Strawberry Jul 23 '20 at 05:10
I see that you are insistent that you are correct. Here is the scenario that you are mssing. I dont appreciate the down vote. Dates 1 Jan 2020 Jun 2020 Dates 2 Jan 2019 Jun 2021 Your solution doesn’t handle this scenario as this will return false: AND Jun 2021 > Jan 2020 AND Jan 2019 < Jun 2020 – yg-dba Jul 23 '20 at 12:26
I dont have anything to fix. The solution provided above works flawlessly. I tested it out recently for my own project. I have years of experience using the OR solution, and how it slows down the MySQL query. Please attempt to fix your answer to include the scenario I gave you and you'll see what I mean... – yg-dba Jul 23 '20 at 12:36
Agreed. It's faster than multiple OR clauses. So why the downvote if you aren't familiar with it? – yg-dba Jul 23 '20 at 14:36
No. It isn't. That's my point. As discussed; it cannot use an index. – Strawberry Jul 23 '20 at 14:53
I didn't say it will use an index. I said it's faster than multiple OR clauses. "That is correct that functions do not use indexes. However, based on a lot of experience with this, this solution works much faster since it's a single WHERE clause instead of 4". Please remove the down vote, as this answer should improve the performance, and it works! – yg-dba Jul 23 '20 at 23:40

OR clauses drastically slowing down MYSQL query

2 Answers2