should I use LIMIT = 1

[piano0011]

Hey guys!

This is a strange issue but I have been trying to implement a forum into my website and I know that i will always get problems when trying to follow someone's else but anyway, he has included the use of limit=1. For some reason, my other codes suddenly won't work and when I mean won't work, I mean that it will display multiple times like a loop unless I also use limit=1. Is this usually the case? Should I always use limit=1? to make sure that only 1 record is displayed?

[requinix]

[Barand]

There is no answer to that question that would be correct for all situations.

[ginerjm]

Do you even know what you are doing?   The 'limit' clause is used to do just that - limit the number of results one wants returned by the query engine. 

 

SO?  What is it YOU want?  Do you want only one record or do you want all the records that match your query question?  And why do you think that we have any idea?

[gizmola]

Every SELECT query returns a result SET.

That set can be:

• Empty Set (no rows)
• 1..n rows

There are many ways to construct a query so that you know in advance it will return exactly one row.  COUNT(*) queries for example.  Another case you know you will get 0 or 1 row is when you query a table by primary key as the only WHERE clause criteria.  You know in advance you will get either 1 row or no rows if the primary key can not be matched.

All LIMIT does, is constrain a result SET that it has multiple rows, so that you are provided a SUBSET of the full SET that would otherwise have been returned.

I don't see LIMIT 1 used all that much, but you frequently will see LIMIT used to facilitate pagination of a large result set as in 

LIMIT offset, count;

If your pages are assumed to display let's say 10 entries, then:

LIMIT 0, 10;

would allow your client to fetch the first 10 entries.  By keeping track of the 'offset' in your navigation you can paginate large result sets fairly simply, so long as you have determined the full size of the result set initially using a count or alternatively the ability of the client library api to tell you the number of rows in the full result set.

[piano0011]

Thanks guys and really appreciate the feedback!