3 Answers
3

MySQL is smart enough to handle those many records. You may read all users and their books in a single query and then display them as you may wish. However, showing those many records on a single web page will impact the response time.

Hence comes pagination - read limited records per page. Though this will mean a SQL query per page but still optimized. And of course you can use some query caching.

A better option could be to show an alphabetical list of users, not necessarily A-Z but also like AB, AC, AD and so on. That way your visitors can directly jump to a particular list. Consider adding pagination to it if the number of users in a given list is too large.

I'm not sure how important is it for your website to show latest updates ASAP, but you may also think on generating XML files, as many as you seem necessary, for example alphabetical and generate your web pages from the XML files. You may update those XML files once every 24h. So, minimum DB load.

And please consider building a search because navigating through those many users could be discouraging.

Sending that many records across the network to the machine running his code, plus the memory hit on that machine, would incur the wrath of the system admins, drastically slow the systems, and waste a lot of CPU. Pagination using offsets into the database records is the ONLY solution that would scale well. Even parsing 5 million XML entries would be slow, even if broken into smaller files. A good DBA could help the OP.
–
the Tin ManJan 7 '12 at 18:37

@theTinMan, of course sending those many records to the client isn't a good way to go and hence I suggested pagination. The suggestion for an alphabetical list is to provide an alternate navigation method. I do not think parsing from the XML files would be slow if you have a logic to directly know which XML file to read; you don't need to read all XML files sequentially
–
AbhayJan 9 '12 at 10:13

I'm not sure who voted you down, probably someone who lacks comprehension, but your answer is reasonable and thought out, so I +1'd you.
–
the Tin ManJan 9 '12 at 21:14

Thank you for the upvote. I didn't mean to put it on you for the downvote if you might have thought so :-)
–
AbhayJan 10 '12 at 2:19

It would be unreasonable to display all the users and their books on the same page. There are, I believe, two possible approaches to solving this:

You can have a index page for the users where you list all the users. Corresponding to each user you can have a "show" page where you display the user's books. This would greatly simplify the resulting database queries as you need to load only the users for the index page, and load only one user's books on his/her show page. That means no complex joins and not a lot of data each time.

If you really want to show multiple users and their books on the same page, then, like someone mentioned in the comments above, you need to use pagination, say load 5 users per page. However, to add to that, you would also need to use eager loading as that could easily turn into an N + 1 problem. You could read more in "Eager loading of associations".

Going back to the first approach, you could even use pagination in that as well; For example, in listing the users or even the books for a user.

As the tin man stated in above comments wouldn't it be very heavy, if i send the complete 500,000 users list in a single requests reponse Even if i go by the 2nd approach I would still be making arounf 500,00 DB queries for that page
–
RossJan 7 '12 at 19:19

Queries with large offsets are inefficient in MySQL; When evaluating a query with an offset of 100000, MySQL has to actually find those 100,000 rows and discard them before it can find the ten rows you end up displaying.

One way around this is to give your application hints: Rather than saying page 10000, say that it's the page where id > x, if you were sorting in primary key order.