On Sat, Dec 17, 2011 at 3:18 PM, Timour Katchaounov <timour@montyprogram.com> wrote:

Mark,

...

I want a variant of the LIMIT clause that limits the number of rows examined during SELECT processing. This should return OK when the

I had a look at the code, and it seems you need a limit on the value of Rows_examined that is currently available in the slow query log. Did I understand correctly that this is what you need?

yes

...

limit is reached -- something like NESTED_LOOP_QUERY_LIMIT. While LIMIT can be LIMIT x and LIMIT x,y. This only supports one argument -- LIMIT_ROWS_EXAMINED x. Are you interested in implementing this feature for me? We will sponsor the work. The purpose of the feature is to prevent queries from taking too long when there isn't a great index and many rows might be filtered.

Have you considered a system variable instead of extending the query syntax? If you rejected this idea, why?

It seems quite logical to me that one may want to set the same limit for more than one query. In this case a system variable would make life easier, because no queries need to be changed. Personally I would prefer not to extend the query language with constructs whose purpose is to compensate for the imperfection of the optimizer.

This isn't about limits on the optimizer. We don't have a great index for some queries and never will. We prefer to stop queries early rather than have them examine 1,000,000 rows. Real users might be waiting on the query response so we really need to finish queries fast and in some cases our apps work as expected were queries allowed to terminate early. I almost always prefer to attach the semantics to the statement and that includes this case. Anyone looking at the statement via a log (slow query, general), SHOW PROCESSLIST, tcpdump should be able to understand what the statement does. Making the LIMIT part of a session variable prevents that. I also don't want to risk an additional round trip to the server and for drivers that don't support multi-statement or for limitations on the code that generates the SQL, I don't want to do: set session limit_rows_examined=1000; SELECT /* with rows examined limit */ ... ; set session limit_rows_examined=0; -- Mark Callaghan mdcallag@gmail.com