List:General Discussion« Previous MessageNext Message »
From:Scott Gifford Date:April 27 2005 3:34pm
Subject:Re: Get a Random Row on a HUGE db
View as plain text  
<gunmuse@stripped> writes:

[...]

> So what I am trying is this.
>
> $last_row ="SELECT from firebase_content LAST_INSERT_ID()";
> $last_row_query = $dbi->query($last_row);
> $last_row_result = $row->id;

LAST_INSERT_ID() only works if you just inserted an element; it's
maintained per-connection.  See:

    http://dev.mysql.com/doc/mysql/en/getting-unique-id.html

If your items are numbered sequentially, try just using:

   SELECT COUNT(*) FROM firebase_content;

to get the count.  That's very fast; it comes from the table summary
information, IIRC.  I use a similar solution for a similar problem,
and have had great luck with it.

To deal better with deleted items, you could periodically renumber
your articles to avoid gaps in numbering.

It would be great if MySQL had an optimization for this case.

----ScottG.
Thread
Get a Random Row on a HUGE dbgunmuse26 Apr
  • Re: Get a Random Row on a HUGE dbRhino26 Apr
  • Re: Get a Random Row on a HUGE dbDawid Kuroczko27 Apr
RE: Get a Random Row on a HUGE dbJay Blanchard26 Apr
  • RE: Get a Random Row on a HUGE dbgunmuse26 Apr
    • Re: Get a Random Row on a HUGE dbPeter Brawley26 Apr
      • RE: Get a Random Row on a HUGE dbgunmuse26 Apr
      • RE: Get a Random Row on a HUGE dbgunmuse26 Apr
        • Re: Get a Random Row on a HUGE dbGary Richardson27 Apr
    • Re: Get a Random Row on a HUGE dbChristian Meisinger27 Apr
    • Re: Get a Random Row on a HUGE dbScott Gifford27 Apr
    • Re: Get a Random Row on a HUGE dbJigal van Hemert28 Apr