Efficiently sorting data from DB using Java?

Question

What we have:

3 MySQL DB tables: user, text, friend

user: username, password, email, etc.

text: username, text, date, etc.

friend: username, friend_username, etc.

Task:

Write an algorithm (in Java) to show 10 latest texts from your friends.

Ultimate target is to have running time within O(n log n).

DB tables can be modified (added new ones) as required.

Data volume: 200 000 users, ~50 text per user.

I would appreciate any ideas, examples, points, etc. Thank you!

(Not a homework. Pure problem, looking for performance improvement)

As the original poster, you have the best viewpoint to say whether or not it's homework. But please realize that questions that are stated the way you did (with something like "Task: Write an algorithm (in Java) to...") generally makes people assume that it's homework. — Jason S
– Jason S, Commented Apr 28, 2009 at 17:14
Also, when you have a query like this (where a larger dataset is reduced to a small dataset) it's almost always faster to do it as a query on the database server. Otherwise the server has to transmit a whole bunch of data to the client to be processed. — Jason S
– Jason S, Commented Apr 28, 2009 at 17:16
Which I believe is a good way to present problem instead of 2 paragraph essay? — Artemij
– Artemij, Commented Apr 28, 2009 at 17:17

Adam Paynter · Accepted Answer · 2009-08-25 15:25:36Z

3

Are you sure this has to be done in Java? Is this not an SQL query?

SELECT text
  FROM TEXT
 WHERE username IN
         (
           SELECT friend_username FROM FRIEND WHERE username = 'YOUR_USERNAME'
         )
 ORDER BY date DESC
 LIMIT 10

edited Aug 25, 2009 at 15:25

answered Apr 28, 2009 at 16:30

Adam Paynter

47.1k33 gold badges152 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Michael Myers Over a year ago

If the assignment (and what else could it be?) was to do it in Java, then you didn't do the homework. ;)

Artemij Over a year ago

IN can be very slow with large volumes of data as far as I know?

Adam Paynter Over a year ago

@Artemij: It depends on which database management software you are using. Which software are you using? Does it offer a tool to explain queries? If so, I would recommend having it explain the query to you. This should help clarify any performance bottlenecks.

Adam Paynter Over a year ago

@Artemij: Sorry, I didn't realize you had edited the question to specify MySQL as your DBMS. If I remember correctly, the MySQL Query Browser supports the "Explain" functionality.

Artemij Over a year ago

@Adam: MySQL Server 5.1 + NetBeans 6.5

|

jsight · Accepted Answer · 2009-04-28 16:33:41Z

1

You didn't say what database, so I'll just make an assumption (Postgres syntax below):

select t.* from text t
    inner join friend f ON f.friend_username = t.username
    where f.username = 'myusername'
    order by t.date desc
    LIMIT 10

answered Apr 28, 2009 at 16:33

jsight

28.5k25 gold badges111 silver badges140 bronze badges

1 Comment

jsight Over a year ago

I'm leaving a pure Java version up to the questioner as a new homework assignment. :)

Collectives™ on Stack Overflow

Efficiently sorting data from DB using Java?

What we have:

Task:

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

What we have:

Task:

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related