Finding all date ranges that overlap a target date range
You can generate a customized index table, for example, that has a row for all year&amp;week pairs that the date range (a certain vacation etc) encompasses. Then you can join dateranges by going through that index table. It will be large, but does avoid large scans as you can just list all vacations that have any weeks that are in common with another vacation, as an inner join.

pseudo example:

create table rangeindex (
  vacation_id bigint, 
  year int, 
  week int, 
  primary key (year,week,vacation_id), 
  index (vacation_id))

select v2.* 
    from vacation v1 
    join rangeindex r1 on r1.vacation_id = v1.vacation_id 
    join rangeindex r2 on r2.year = r1.year and r2.week = r1.week
    join vacation v2 on v2.vacation_id = r2.vacation_id
where v1.vacation_user = ?
    -- and the usual start/end comparisons to filter unwanted pseudo hits


Similar things are used for example spatial coordinate stuff, dividing linear coordinates to grid cells that can be indexed and scanned more easily.

Update: fixed primary key