Best way to sort the data : DB Query or in Application Code

Question

I have a Mysql table with some data (> million rows). I have a requirement to sort the data based on the below criteria

1) Newest

2) Oldest

3) top rated

4) least rated

What is the recommended solution to develop the sort functionality

1) For every sort reuest execute a DBQuery with required joins and orderBy conditions and return the sorted data

2) Get all the data (un sorted) from table, put the data in cache. Write custom comparators (java) to sort the data.

I am leaning towards #2 as the load on DB is only once. Moreover, application code is better than DBQuery.

Please share your thoughts....

Thanks, Karthik

you not only need to retrieve all your data but also order them in different ways all at once.. probably good idea to back to the drawing board and rethink the design — Baz1nga, Apr 12 '12 at 21:58

score 3 · Answer 1 · answered Apr 12 '12 at 22:02

Do as much in the database as you can. Note that if you have 1,000,000 rows, returning all million is nearly useless. Are you going to display this on a web site? I think not. Do you really care about the 500,000th least popular post? Again, I think not.

So do the sorts in the database and return the top 100, 500, or 1000 rows.

Dmytro Shevchenko · Answer 2 · 2012-04-12T22:07:13.447

Fetching a million rows from the database sounds like a terrible idea. It will generate a lot of networking traffic and require quite some time to transfer all the data. Not mentioning amounts of memory you would need to allocate in your application for storing million of objects.

So if you can fetch only a subset with a query, do that. Overall, do as much filtering as you can in the database.

And I do not see any problem in ordering in a single queue. You can always use UNION if you can't do it as one SELECT.

score 0 · Answer 3 · answered Apr 12 '12 at 21:59

It's much faster to do it in the database:

1) the database is optimized for I/O operations, and can use indices, and other DB optimizations to improve the response time

2) taking the data from the database to the application will get all data into memory. The app will have to look all the data to redorder it without optimized algorithms

3) the database only takes the minimun necessary data into mamemory, which can be much less than all the data whihc has to be moved to java

4) you can always create extra indices on the database to improve the query performance.

score 0 · Answer 4 · answered Apr 12 '12 at 21:59

I would say that operation on DB will be always faster. You should ensure that caching on DB is ON and working properly. Ensure that you are not using now() in your query because it will disable mysql cache. Take a look here how mysql query cache works. In basic. Query is cached based on string so if query string differs every time you fetch no cache is used.

score 0 · Answer 5 · answered Apr 12 '12 at 22:04

AFAIK usually it should run faster if you let the DB sort your data.

And regarding code on application level vs db level I would agree in the case of stored procedures but sorting in SELECTs is fine IMHO.

If you want to show the data to the user also consider paging (in which case you're better off with sorting on the db level anyway).

score 0 · Answer 6 · answered Apr 12 '12 at 22:23

You do not have four tasks, you have two:

sort newest IS EQUAL TO sort oldest AND sort top rated IS EQUAL TO sort least rated.

So you need to make two calls to db. Yes sort in db. then instead of calling to sort every time, do this:

1] track the timestamp of the latest record in the db
2] before calling to sort and retrieve entire list, check if date has changed
3] if date has not changed, use the list you have in memory
4] if date has changed, update the list

score 0 · Answer 7 · edited May 23 '17 at 11:49

I know this is an old thread, but it comes up in my search, so I'd like to post my opinion. I'm a bit old school, but for that many rows, I would consider dumping the data from your database (each RDBMS has it's own method. Looks like MySQLDump command for MySQL: Link ) You can then process this with sorting algorithms or tools that are available in your java libraries or operating system.

Be careful about the work your asking your database to do. Remember that it has to be available to service other requests. Don't "bring it to it's knees" servicing only one request, unless it's a nightly batch cycle type of scenario and you're certain it won't be asked to do anything else.

Best way to sort the data : DB Query or in Application Code

7 Answers7