maria-developers team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

On Sat, Dec 17, 2011 at 3:18 PM, Timour Katchaounov
<timour@xxxxxxxxxxxxxxxx> 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@xxxxxxxxx