Styjun

I have a table like so:

CREATE TABLE mytable (
 id INT(10) auto_increment PRIMARY KEY,
 from DATE(10) NOT NULL,
 before DATE(10) NULL,
 reference_id INT(10) NOT NULL,
)

So there are rows that reference another table (with reference_id). Those references have a date range (from/before) in which they are applicable. For each reference_id there may be many entries which usually have no gaps:

id | from | before | reference_id
-------------------------------------------
1 | 2019-03-01 | 2019-03-05 | 5
5 | 2019-03-05 | 2019-03-09 | 5
8 | 2019-03-09 | NULL | 5

(There might be entries for other reference_ids between them.) An entry begins where the previous ended. Now I want to find all entries that have a gap between them, where the from is later than the before of the preceding. For example (change in row 2, column from):

id | from | before | reference_id
-------------------------------------------
1 | 2019-03-01 | 2019-03-05 | 5
5 | 2019-03-06 | 2019-03-09 | 5
8 | 2019-03-09 | NULL | 5

Row 2's from is one day later than row 1's before, that's a gap. The problem: for rows 3 and 1 the same is true, but they should not be considered as result because they have another row between them.

What I came up with is this:

SELECT *
FROM mytable mt1
INNER JOIN mytable mt2 ON mt1.reference_id = mt2.reference_id AND mt1.id != mt2.id
WHERE mt1.before IS NOT NULL
 AND mt1.from < mt2.from
 AND DATE_ADD(mt1.before, INTERVAL 1 DAY) = mt2.from
 AND NOT EXISTS(SELECT * FROM mytable mt3 WHERE mt3.id BETWEEN mt1.id AND mt2.id)

However, this (the EXISTS) is incredibly slow. Is there a better way to do this?

[edit]The query just finished and I didn't get any results although I'm definitly expecting some. So it's not only slow but also incorrect.[/edit]

Execution plan:

1,PRIMARY,mt1,ALL,"mytable_48d78c2b,mytable_261384ee,mytable_849034da",,,,3313021,Using where
1,PRIMARY,mt2,ref,"mytable_48d78c2b,mytable_849034da",mytable_849034da,4,db.mt1.reference_id,1,Using index condition; Using where
2,DEPENDENT SUBQUERY,mt3,index,PRIMARY,mytable_48d78c2b,3,,3313021,Using where; Using index

(Note that from is a very bad choice for a column name as it is a reserved keyword. But well, it is valid, so there we go.)

Assuming no overlap, you can use the lag function to look up the before of the previous row: lag(before, 1) over (partition by reference_id order by before) as previous_before. From there on, if it easy to check if there is a gap, if from > previous_before

select
 `from`, before, `reference_id`
 , `from` > lag(before, 1) over (partition by reference_id order by before) as has_gap
from mytable

What the lag does is to find the before value of the previous row, previous being determined by the order by clause of the window function. If from and before are identical, there is no gap.

This query gives you row with a gap before them, you can use the lead function in the same way to get rows with a gap after them.

Note that MariaDB has window functions since 10.2.2 only.