[thesite] friday is a good day..

[jeff]

joshua,

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: Joshua OIson
:
: You say that we need to grab the entire recordset
: to generate the page throughs?  I say not to that.
: To generate the page throughs all we need is to:
:
: 1) know how many total records we are dealing with
: 2) determine which of the database records fall
:    within the current "page" we are viewing
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

yes, quite true.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: The first piece of information can be done with a
: COUNT and only needs to be recalculated when an
: article is added, thus disrupting the count.  So,
: figure out what that number is and store it somewhere.
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

are you suggesting an application scoped variable?  i can envision the logic
necessary to make that hit as minimal as possible and happen only when
absolutely necessary.

:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: The second piece is a hashing algorithm of some sort.
: You can make a counter that ONLY counts the articles.
: 1 is the oldest article, max is the newest.  That way,
: all you do is make the newest article the next number.
: Use this number in conjunction with the number we
: already know (how many articles there are total) which,
: btw, is the same number, to figure out which subset to
: query.  Then you can put the constraints into the query
: without worrying about limits etc.  Just say I want
: article max_id through max_id-10!  Where max_id is the
: id of the newest article.
:
: Shouldn't be too tough.  Waddaya think?
:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

i think we're saying almost the same thing, but here's what i'd do, if this
is really a problem worth throwing some additional development time into.
i'd add a rank column to the database.  add a trigger (or build the logic
into the middleware) to update the rank column on the entire table based on
the datemod and signoff columns (1) whenever a new article is approved
(which would automatically .  those articles that have a signoff value of 1
(approved) get a rank from 1 (newest) to max rank (2) (oldest).  we would
then use the startrow and maxrows attributes within the query instead of the
cfoutput.  the query would select only those articles that had a rank that
was >= the startrow and <= startrow + maxrows - 1.  whenever an articles
signoff status changes from 1 we'd simply update the rank column to adjust
the rank of all the articles to fill the gap (3).  approving an older
article and adjusting the rank of all approved articles to open a gap in the
appropriate place would be alittle more difficult, but not impossible (4).

the major difference between what you and i are saying is the direction of
the "rank".  i'd rank from 1-max in the order you want to display the
articles.  you're suggesting ranking from max-1.

the one disadvantage to this is that the query for each page results view
isn't going to be cached for you so it'll have to create a new query.  i
still see it being a huge performance gain though because your resultset
will always be smaller than the current query's resultset.

now, the order by can be attached to the rank column which should already be
indexed as opposed to the datemod column which has no guarantee of being
indexed or of the database actually using the index even if it exists.

(1):  UPDATE content
         SET rank = rank + 1
       WHERE rank >= 1

(2):  SELECT Count(*) AS max_rank
        FROM content
       WHERE signoff = 1

(3):  UPDATE content
         SET rank = rank - 1
       WHERE rank >= deleted.rank

(4):  UPDATE content
         SET rank = rank + 1
       WHERE datemod >= inserted.datemod
         AND contentid > inserted.contentid

thanks,

.jeff

name://jeff.howden
game://web.development
http://www.evolt.org/
mailto:jeff at members.evolt.org