Joined: 13 Nov 2003 Posts: 1397 Location: Karlovac
Posted: 06.02.2005 13:29 Post subject: SQL limit
Dakle, da li je u redu u SQL-u koristiti LIMIT naredbu (konkretno - za ispis rezultata pretrage ili tako neceg po stranicama - ko sta mi3 ima recimo u galerijama radove po stranicama).
Jer kao ta naredba nije sluzbeno podrzana po tim nekim standardima (nema ju ni na w3schools recimo tamo pod njihovima tutorialima sam gledao)?
Sto vi koristite (PHP i MySQL je u pitanju).
Fala.
_________________ Bolje biti malo lud, nego malo pametan.
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants.
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax.
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
mysql> SELECT * FROM table LIMIT 95,18446744073709551615;
With one argument, the value specifies the number of rows to return from the beginning of the result set:
mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.
Vidis da je podrzano
Na W3schools je to vjerojatno beginners tut, da previse ne petljaju po SQL kodu, samo najjednostavnije dobivanje rezultata iz baze
_________________ FRENCH GUARD: No chance, English bed-wetting types.
I burst my pimples at you and call your door-opening request a silly thing, you tiny-brained wipers of other people's bottoms!Flikrac
koliko sam razgovarao sa osobom koja baze ne koristi samo za web naredba LIMIT nije podrzana standardom SQL84 pa tako kod napisan sa naredbom limit nije prenosiv na npr. ms sql ili neke druge baze... jel ima tko da zna vise o tome?
e sad, koliko bi sljedeci kod usporavao server (i gubi li se uopce na performansama njime)?
Code:
SELECT TOP 10 * FROM tablica WHERE id NOT IN (SELECT TOP 20 id FROM tablica ORDER BY id) ORDER BY id
(kao zamjena za:)
Code:
SELECT * FROM tablica LIMIT 20,30
edit: na w3schools je __SQL__ tutorial, a ne MySQL, Access, MS SQL, Oracle ili neki treci tutorial... a SQL standard ne postuju sve baze jednako, tj. dodaju neke svoje mogucnosti...
Svakako je bolje sto vise toga rijesiti u samom SQL upitu, a ne kad dohvatis upit filtrirati dobivene redove... Bez obzira da li je LIMIT u pitanju, TOP ili nesto drugo.
Joined: 13 Nov 2003 Posts: 1397 Location: Karlovac
Posted: 06.02.2005 15:58 Post subject:
imbrod wrote:
Svakako je bolje sto vise toga rijesiti u samom SQL upitu, a ne kad dohvatis upit filtrirati dobivene redove... Bez obzira da li je LIMIT u pitanju, TOP ili nesto drugo.
pa ok, ovo sto je __bj napravio je sve u jednom SQL upitu, i stvarno se reducirati upitom ne moze vise nego sta je (ako trazim nesto u bazi napravi %LIKE% ali ostatak moram filtrirati po broju).
Ali pitanje je da li je
a) LIMIT uopce dobro koristiti (svi tu brijemo nesto o standardima, a LIMIT nije kao u standardu)
b) te da li je
Code:
SELECT TOP 10 * FROM tablica WHERE id NOT IN (SELECT TOP 20 id FROM tablica ORDER BY id) ORDER BY id
sporije od
Code:
SELECT * FROM tablica LIMIT 20,30
recimo da govorimo o bazi podataka s brdom upisa (10 000, 20 000, 100 000...)
_________________ Bolje biti malo lud, nego malo pametan.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum