Prohibit MySQL from using full table scan on a query

Question

Is there any way I can prohibit 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;

Is only efficient if X satisfies the condition and there is at least 1 row returned, but if the condition cannot be satisfied by any data in the table, full scan will be performed, which can be very costly.

I don't want to optimize this particular query, it is just an example.

EXPLAIN on this query with X in or outside of 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 show much better information. For X outside of range:

Handler_read_prev 84181
Key_read_requests 11047

In range:

Handler_read_key 1
Key_read_requests 12

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

UPDATE. I can't accept my own answer, because it doesn't really answer the question (HANDLER is a great feature, though). It seems to me that there is no general way to prevent MySQL from doing a full scan. Although, simple conditions like key='X' will be considered as "impossible where", more complex things like BETWEEN will not.

Answer 1

You could write a "fully covered" subquery that only uses data that is available in indexes. Based on the returned primary key, you can look up the rows in the master table.

The following query is fully covered by indexes on (id), (B,id), and (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

Each SELECT uses one index: the innermost the index on (B,id); the middle SELECT uses the index on (C,id), and the outer SELECT uses the primary key.