Prevent MySQL from using full table scans on request

Question

Is there any way to prevent MySQL from performing a full table scan when the result was not found using indexes?

For example, this query:

SELECT *
FROM a
WHERE (X BETWEEN a.B AND a.C) 
ORDER BY a.B DESC 
LIMIT 1;

It is effective only if X satisfies the condition and at least one row is returned, but if the condition cannot be met by any data in the table, a full scan will be performed, which can be very expensive.

I do not want to optimize this particular request, this is just an example.

EXPLAIN in this query using X within or outside the range:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE a range long_ip  long_ip 8 \N 116183 100.00 Using where

STATUS VARIABLE shows much better information. For X out of range:

Handler_read_prev 84181
Key_read_requests 11047

In the range:

Handler_read_key 1
Key_read_requests 12

If only there was a way to prevent Handler_read_prev from the ever-growing past.

. , (, HANDLER - ). , MySQL . , key = 'X', " ", , BETWEEN, .

Answer 1

:

HANDLER a OPEN;
HANDLER a READ BC <= (X);
HANDLER a CLOSE;

BC - (B, C). B DESC,

SELECT *
FROM a
WHERE (X BETWEEN a.B AND a.C) 
ORDER BY a.B DESC 
LIMIT 1;

, X a, , aC X, , X , .

, .

Answer 2

" " , , . .

(id), (B, id) (C, id):

select *
from a
where id in (
    select id
    from a 
    where x <= C
    and id in (
        select id
        from a
        where B <= X 
    )
)
limit 1

SELECT : (B, id); SELECT (C, id), SELECT .