On Fri, Aug 19, 2016 at 7:02 PM, Victor Blomqvist <vb@viblo.se> wrote: > What I want to avoid is my query visiting the whole 1m rows to get a result, > because in my real table that can take 100sec. At the same time I want the > queries that only need to visit 1k rows finish quickly, and the queries that > visit 100k rows at least get some result back.
You are going to have problems with that. If you just want to limit it to max 100k rows, max 10 results my solution works, probably better as nested selects than CTEs, but someone more knowledgeable in the optimizer will need to say something ( or tests will be needed ). This is because "the queries that visit 100k rows at least get some result back." may be false, you may need to visit the whole 1M to get the first result if you are unlucky. Just set ap=999 where id=1M and ask for ap>=999 and you've got that degenerate case, which can only be saved if you have an index on ap ( even with statistics, you would need a full table scan to find it ).
If you are positive some results are in the first 100k rows, then my method works fine, how fast will need to be tested with the real data. You can even try using *10, *100, *1k of the real limit until you have enough results if you want to time-limit your queries.
Francisco Olarte.
Thanks! A sub select seems to do it.
I didnt think of it before, guess I got blinded by the CTE since usually its the other way around and the CTE is the answer to the problem.
But seems like the easy solution with a good old sub select fixes it. Now I feel a bit embarrassed for such a easy answer :)
Checking these two queries I can see that the first one visits the max 50 rows its allowed to and returns 5 rows, while the second one finish off after 13 rows fetched and returns the full 10 rows.
select * from (select * from b order by id limit 50) x where age_preference%10 < 1 order by id limit 10
select * from (select * from b order by id limit 50) x where age_preference%10 < 5 order by id limit 10
