Thread: does LIMIT save time?

Hi,

is using SELECT LIMIT faster than using SELECT without LIMIT on
the same query? If yes, in what way? What is if I use ORDER in the
SELECT clause?

Is it possible to get the *total* number of rows selected (ie. doing
the query virtually without LIMIT) when using a LIMIT clause?

Thanks for help,

Dirk

>
>
> Hi,
>
> is using SELECT LIMIT faster than using SELECT without LIMIT on
> the same query? If yes, in what way? What is if I use ORDER in the
> SELECT clause?

    First  of  all,  LIMIT reduces the amount of data sent to the
    client application.  Thus  it  reduces  network  traffic  (or
    interprocess communication if run on the same system), and it
    saves wasted memory in the client to buffer received  results
    that aren't wanted.

    For  the  backend processing time it depends on the execution
    plan generated by the optimizer if  much  or  less.   If  the
    query  plan tells the executor that the result must be sorted
    or grouped,  then  the  entire  result  set  must  first  get
    collected and sorted before the correct portion of the result
    set could be returned. Such a sort or group step is caused by
    the  ORDER  or  GROUP  clauses.  The  optimizer  has  limited
    capability to realize if the index he has choosen for a  scan
    will  already  present  the data in the wanted order and then
    suppress the sorting.

    So in general, LIMIT itself could never hurt.  ORDER can  but
    someone  who  cares  for  speed  is normally willing to setup
    appropriate multi key indices to prevent sort steps.

>
> Is it possible to get the *total* number of rows selected (ie. doing
> the query virtually without LIMIT) when using a LIMIT clause?

    Absolutely not. In  the  optimal  case  (no  sort  step)  the
    executor  aborts  the  table scans when reaching the limit. A
    well placed LIMIT can dramatically reduce disk IO.  Now  look
    at  the  main road and count the red cars passing by over the
    day. After 10 minutes you stop - do you know  how  many  cars
    will have passed at midnight? The executor is exactly in that
    situation - he stop's scanning so he can't know.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Jan Wieck writes:
>     So in general, LIMIT itself could never hurt.  ORDER can  but>     someone  who  cares  for  speed  is normally
willingto setup>     appropriate multi key indices to prevent sort steps.
 

Just to know, how can I prevent sorting by using multi key indices?


Thanks for your elaborte answer,

Dirk