2 added 529 characters in body
source | link

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    from_field DESC
LIMIT 1 ;

or

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).


The above queries will still be not very efficient when there is no match at all, as a large part of the index will have to be scanned.

If you are 100% sure that there are no overlaps of the intervals, you can use this version, which will only need an index on (from_field) and do an index seek only:

SELECT * FROM my_table
WHERE from_field =
  ( select MAX(from_field) from my_table 
    where 
      (from_field <= 1533177608)
  )
  AND 
    (to_field   >= 1533177608) ;

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    from_field DESC
LIMIT 1 ;

or

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    from_field DESC
LIMIT 1 ;

or

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).


The above queries will still be not very efficient when there is no match at all, as a large part of the index will have to be scanned.

If you are 100% sure that there are no overlaps of the intervals, you can use this version, which will only need an index on (from_field) and do an index seek only:

SELECT * FROM my_table
WHERE from_field =
  ( select MAX(from_field) from my_table 
    where 
      (from_field <= 1533177608)
  )
  AND 
    (to_field   >= 1533177608) ;
1
source | link

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    from_field DESC
LIMIT 1 ;

or

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).