How many rows should I SELECT?

Question

I've got a sql database of 100,000 projects.

When I do a SELECT query to display to the user their project titles, how many projects should I display?

Would grabbing all 80,000 projects (projects relevant to the user) with the SELECT be considered bad practice? (i'm assuming it is).

It's not very clear what goal of this is. I believe you will be able to get better answers if you more clearly define why you need to SELECT all or not SELECT all. — Apealed, Sep 15 '19 at 02:36
Should i be limiting my sql queries to return say 10 results (like Google) or can I just display all of the results? — user1406186, Sep 15 '19 at 02:38
Overall, it's better practice to query only what you need. You likely won't need to query all 80,000 rows. Especially if you're only sampling some data/rows. So 10-50 rows maybe be a better choice than 80,000. — Apealed, Sep 15 '19 at 02:41
you for sure must show a small reasonable amount of results to the user. assuming server side pagination, you should use LIMIT and OFFSET clauses. — Sombriks, Sep 15 '19 at 02:42
Thanks luckeyelijah and Somebriks. That's the info I was after — user1406186, Sep 15 '19 at 02:44
IMHO good practice is to let the user decide, how many records they want to see per page. — Paul Spiegel, Sep 15 '19 at 17:48

score 1 · Answer 1 · answered Sep 15 '19 at 02:39

1

You can use LIMIT / TOP / ROWNUM clause to get a limited rows.

      SELECT column_name(s)
      FROM table_name
      WHERE condition
      LIMIT number;

answered Sep 15 '19 at 02:39

Mukul Kumar Jha

1,062
7
19

You can read more here https://www.w3schools.com/sql/sql_top.asp – Mukul Kumar Jha Sep 15 '19 at 02:43

score 1 · Accepted Answer · answered Sep 15 '19 at 02:59

I believe there are two questions to consider here:

First Consideration

It's important to consider whether a user can comprehend and navigate 80,000 projects. Generally speaking, I think not.

For example, Amazon.com may have 80,000 books "relevant" to my interests. But their interface provides me with filters and recommendations. I never need to confront 80,000 records to find just the few which interest me in the present moment.

Second Consideration

It's important to consider when to transmit those 80,000 records to the user interface (from server to client) and in what batch size should the client store and manipulate those records.

Prior to LTE mobile networks, I'd recommend that a small batch size (say between 10 and 100 records) be sent to the user interface. With SQL, it's possible to use the LIMIT keyword (as @mukul-kumar-jha suggested). You can also perform 'pagination' as described here (What is the best way to paginate results in SQL Server).

But, if those 80,000 records are not long (say, for example, less than 1 megabyte of data for the entire recordset), I can argue there may be benefit in transmitting those records to the client all at once then manipulating the data in the client with filters and such.

Whether to perform pagination at the database, or to filter within the client, depends a lot on a few factors: what's the users' connection speed; what do they pay for bandwidth; is the client sufficiently powerful to manipulate large data sets; how many round trips to the database need to occur for each user on average.

Good luck!

Also, never forget indexing. Sending a batch of small view will require WHERE condition that needs to be EXPLAIN-ed, and indexed. Further, there is a typeahead concept to search something. https://ng-bootstrap.github.io/#/components/typeahead/examples — Bimal Poudel, Sep 15 '19 at 07:34

score 0 · Answer 3 · answered Sep 15 '19 at 02:43

The LIMIT clause can be used to constrain the number of rows returned by the SELECT MySQL statement. You can pass two arguments to the LIMIT constrain both be nonnegative integer constants.

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

This way you can create pagination for the results.

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows

score 0 · Answer 4 · answered Sep 15 '19 at 09:33

You can use LIMIT & OFFSET constrain for it. Syntax of it like:

LIMIT OFFSET,Value After Offset

This way you can create pagination for the results.

SELECT * FROM tbl_name LIMIT 0,10;  # Retrieve rows of 0-10

SELECT * FROM tbl LIMIT 10,20;     # Retrieve rows of 10-20

How many rows should I SELECT?

4 Answers4

First Consideration

Second Consideration