LIMIT and subqueries

Domas initial request:

Essentially we want to be able to use:

SELECT * FROM ( SELECT ... WHERE x=123 LIMIT 1000000 ) WHERE y=456 LIMIT 1000;

Mostly this is to allow paging over large datasets that don't have that great selectivity - so we want to be able to return within reasonable time, even if x=123 condition matches billions of rows, but
y=456 matches very few.
Currently the internal subselect would get materialized by MySQL and wouldn't be used as streaming dataset, which makes it prohibitive on OLTP machines.

In certain cases having DISTINCT not materialize would also be extremely useful for us, but that would probably somewhat different task? (we want to optimize b=Y,c=Z fetches on (a,b,c) indexes).