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(at)debis(dot)com (Jan Wieck) #

Jan Wieck writes:

> 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.

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

Thanks for your elaborte answer,

Dirk