dongren9966 2016-04-01 01:23
浏览 396
已采纳

获取最后x条记录的最佳方法是什么? 使用MySQL Limit和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.

  • 写回答

2条回答 默认 最新

  • dsaxw4201 2016-04-01 01:27
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 我想通过python处理sybase IQ库的一些数据,但是不知道sybase IQ库怎么连接,之前连接都是用linux上的 dbisql
  • ¥15 安装llama-cpp-python==0.1.83失败
  • ¥20 管道轴向耦合水击问题
  • ¥60 补全networkx TODO部分。
  • ¥15 有内推吗,云计算linux运维方向
  • ¥30 sort cuteSV.vcf by bcftools用IGV可视化出现报错
  • ¥100 SOS!对STK中导出的天体图像进行质心提取有没有人做过啊
  • ¥15 python 欧式距离
  • ¥15 遗传算法解决有工序顺序约束的大规模FJSP问题
  • ¥15 企业消防水炮塔设计方案