Performance help

Anon

This table - Friends, contains 99999
records.

I run two SQL script with php.

1) Select * From Friends.

2) Select * From Friends Limit 20.

Which run faster ?

The 1) one will search 99999 records and
return 99999 records.

I wonder if i use 2), is it still search
through 99999 records and return the lastest
20 records? the point is "is it still
through 99999 records?".

Anon

The second will be faster.
Not because it searches fewer records, but because it doesn't have to fetch as many records and store them in memory as the first query.

Remember, the LIMIT function only limits the number of results you see, not the number of results the dbserver finds.

Anon

Remember, the LIMIT function only limits the number of results you see,
not the number of results the dbserver finds.

Really? Are you sure that it's a requirement of SQL92 that a query
optimizer be so unintelligent?

Since queries in the absence of an ORDER BY clause are inherently
unordered, I would have thought that returning <i>any</i> 20 rows
whatsoever would have correctly fulfilled this request (even if those 20
happen to be still in cache somewhere in the backend as the result of a
previous very complicated query, as long as the optimizer thinks that
will be faster than going back to the disk for the first 20, or any other
particular 20.)

Anon

For queries without a where clause yes, the limit would stop the search after 20 rows because 20 rows=20 results.

Anon

how about i user order by for 2) ???

will the database scan through all the record
??

Anon

I tried this on postgresl with a randomly created 30000 row database of two text fields, two int fields, and one serial field (autoincrementing unique int).

select * from test_table limit 20;

response time, ~0.1 seconds.

select * from test_table order by test limit 20;

resonse time: ~2.0 seconds

select * from test_table order by test;

response time: ~5.0 seconds

then I ran vacuum analyze, all other times were the same, but

select * from test_table order by test;

ran in about 2.5 seconds, or nearly the same as the one with the limit.

the point is that this depends highly on which database you are running and which speed tricks you know and or have applied to it.