MySQL Tracking – How to Keep Track of Selected and Fetched Rows

database-designjavaMySQLpaging

I am working on a small website using Java where the user posts are stored in MySQL, I am getting the posts 5 at a time (5 rows using LIMIT 5) by most recent one (using timestamp DESC) and send it to the front-end .

The problem I am trying to solve is to be able to fetch the next 5 that are not the same rows that were fetched first…and so on. So I need to find away to keep track of which rows were selected and fetched in the last query and avoid getting them again.

The idea is that when the user scrolls down to the end, AJAX will send a request to servlet and the servlet will get the next 5 rows from MySQL.

I've been trying to find away by experimenting with the database as well as some online search, could not find a way so far.

Best Answer

Although you can track which rows were selected last using MySQL Enterprise Audit, this would be a little like using a Saturn V rocket to go to the corner store... The cost would be ridiculously high for such a small function.

Since you are ordering rows by timestamp, I am assuming your timestamp value is unique. Wouldn't adding a select clause like where <mytimestampcolumn> < [smallest timestamp column value from last result set]; take care of the requirement?

This would have the additional benefit of making your application scale better relative to other alternatives, such as retrieving the complete result into a cursor and having the servlet fetch the result set 5 rows at a time.

Related Solutions

The Database Session is automatically Getting Killed

I would hope you have some session pooling going on, or at least one database connection per thread. This is not clear from your description. In a multi-threaded design, I would not close connections in the threads unless they make their own connections (which I would consider a bad design).

I would get the process running with one thread, then add an additional thread. Once you are comfortable you have that multi-threading gives you the results you want, you can scale up.

You could turn on auditing on the Oracle side to see what is really happening.

You didn't say how long the queries take to run. If they take minutes, to run you need to ensure you have keep-alive on the network connections. Otherwise, the network connection may get dropped.

EDIT: Check the resource limits applicable to the userid you are connecting with. These might get your connections closed. You may also be getting killed off due to exceeding resource limits. I would expect the reason for disconnect to be in the SQL Error text for the connection.

Given how long these queries are taking, you may want to investigate optimizing the queries. Unless you have lots of memory and processors on the database server you may find that the queries run faster when run serially. You may be forcing data out of the buffer pool with this many long queries running in parallel.

Mysql – Fetching User Recent Status

You have not defined any indexes, so this query would be slow even without sorting, because it needs a full table scan.

You should define a combined index on (uid, id). The first part helps in finding all rows WHERE uid = X. If these have been found, the second part can be used to sort the result set without the need to use filesort.

Use this create table statement

CREATE TABLE userstatus (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  status TEXT NOT NULL,
  datentime TIMESTAMP NOT NULL,
  visible INT (3) NOT NULL DEFAULT 0,
  statustype CHAR (16) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX (`uid`, `id`)
);

and see this and your query in action in this sqlfiddle.

If you are interested in being capable to do similar improvements on your own, you should start by learning to read the output of EXPLAIN.

Best Answer

Related Solutions

The Database Session is automatically Getting Killed

Mysql – Fetching User Recent Status

Related Question