0

For news-ticker applications, like the one in Facebook, we see that as we scroll further, older news appear. Now surely the news are inserted into the table as they occur, so a normal selection would always retrieve older records earlier, whereas here the reverse occurs. I assume the way it is done is that when the user scrolls down to the end, FB sends an Ajax request with the id of the last news-id currently present in the ticker (couldn't identify for sure in Firebug, FB sends loads of data!), the PHP queries the DB, flips the result set according to the time column, then extracts the say next 5 records following the one with the received id. Now such tables are huge, so flipping them frequently surely takes a heavy toll on the DB. So is there any way to achieve this without flipping?

SexyBeast
  • 7,913
  • 28
  • 108
  • 196
  • 2
    I think that sorting on time is the way to go. I'm not sure whether you can rely on mysql to always return records in order of original insertion time. Also, I'd expect that having mysql sort the results is much faster than having php do it, but you could benchmark that pretty easily. – octern Jul 27 '12 at 18:40
  • 1
    Extracting the five records in the DB before the one with the given row ID, then sorting those in descending order based on time? – JAB Jul 27 '12 at 18:40
  • How do I **extract the five records in the DB before the one with the given row ID**? – SexyBeast Jul 27 '12 at 18:41
  • Well, right,that does seem to solve the problem... – SexyBeast Jul 27 '12 at 18:45
  • MySQL should always return rows based on the order they were inserted, unless a clustered index is declared on some other column... – SexyBeast Jul 27 '12 at 18:48
  • 1
    `execute("SELECT * from DB WHERE rowid BETWEEN ? AND ?", lastID-1, lastID-6)` or something (it doesn't specifically have to be 'rowid' [I forgot that mySQL doesn't have that as an implicit column for all tables], but as long as they're using a auto-incrementing integral ID as the primary key for the records, which is the norm for most big databases, I believe, it should work). I'm sure there are ways of doing it that don't depend on the records being inserted in order, either, but those would likely be a bit more complicated. – JAB Jul 27 '12 at 18:49
  • 1
    When I said "inserted in order", I meant with monotonically nondecreasing timestamps. – JAB Jul 27 '12 at 18:52
  • Does this answer your question? [SQL best practice to deal with default sort order](https://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order) – philipxy Dec 10 '19 at 09:41

3 Answers3

3

If you don't specify an ORDER BY variable in your query, MySQL is not guaranteed to return the records in order of insertion. See this answer for more detailed information. If you want to be sure you're getting the most recent rows, you need to have an insertion time column and sort on that.

If you're sorting on time desc, then you can use the usual LIMIT clause to request just the first 5 records (i.e., the 5 most recent), or the 5 most recent after a certain ID, etc.

Community
  • 1
  • 1
octern
  • 4,825
  • 21
  • 38
  • Oh, I didn't know that. I thought that MySQL always returns rows in the order they were inserted, unless of course a clustered index on some other column forces it do otherwise. – SexyBeast Jul 27 '12 at 19:07
  • 1
    +1. @Cupidvogel: there is absolutely NO guarantee that MySQL will return rows in any particular order EXCEPT the order specified in the `ORDER BY` clause. – spencer7593 Jul 27 '12 at 21:05
1

Yes, keeping in mind that we're speaking about this very abstractly. If you wanted to access an indexed collection in reverse order.

$collection = array(); // Filled through request to server
....
for ($i = sizeof($collection)-1; $i >= 0; $i--) {
    echo $collection[$i]
    .... // execute some action based on access to 
}

Which is to say, there's no reason you can't access an array from its last index.

OYRM
  • 1,395
  • 10
  • 29
1

specify order by in your sql statement to get the oldest first. and do where id > last_id to get the rows after the last_id

Neel Basu
  • 12,638
  • 12
  • 82
  • 146