phpmysqlphp-7mysql-select-db

What is the best approach to getting the last x records? Using MySQL Limit and ORDER DESC?

I was wondering if there are any other best/better practices then this as far as performance or optimizations.

EDIT: I'm using PHP7. Should have made that clear, I apologize. I'm doing a volunteer project for a local non-profit and it's setup like this:

Table: Volunteer

pk: v_id

So what I'm doing is: SELECT * from Volunteer ORDER BY v_id DESC LIMIT 25;

(They want the last 25 to display currently for the "last logs" report.)

EDIT2: The only reason I'm asking this now, we've hit 10k volunteer logs in the system and I'm starting to realize MON-FRI they can add anywhere from 50-100 (or more) logs per day so it quickly adds up.

Solution

  • If you can do it programatically ORDER it ASC with LIMIT X and then loop through the records backwards.

    If you give us your language (PHP, node(javascript), java, etc) we can help you with the backwards loop, but it goes something like this :

    for(i=rows.length-1;i>=0;i--){
   //do your stuff here
}

    If you MUST do it in MySQL

    SELECT your results ASC with LIMIT X as a subquery and then wrap in a query ORDER DESC

    SELECT
*
FROM
(
    SELECT
    somecol
    FROM
    sometable
    ORDER BY
    the_date ASC
    LIMIT 100
) as a
ORDER BY
the_date DESC

    UPDATE: The way the question was asked at first it sounded like you wanted the last X results, ordered DESC. I will leave my answer in place in case anyone else comes here looking for that.

    What you are doing now with the ORDER BY and LIMIT is the optimal way to do it. To optimize, you may want to SELECT only the columns you need, and make sure you have a unique index on v_id. If you need more optimization than that, you may want to consider archiving old data and vacuuming frequently.