[thelist] MySQL queries and AUTO_INCREMENT

[Luther, Ron]

Bernardo Escalona-Espinosa described a problem where he wants to pull a
random photo out 
of an ever changing db collection of photographs:


Hi Berns,


First of all, I agree with Ken (et alia) that auto-increment is the
wrong tool for this task.

Here is an outline of *one way* to do this that might be workable and
not too tricky 
to implement:

Assumption - I'm guessing that this photo collection isn't quite as
large and doesn't 
have quite as much update activity as Phil's 'photo.net'.  I'm also not
that familiar 
with triggers and sprocs so I wanted to suggest something that stayed
away from those.

How about a kinda simple four table design?

(1) The photo table.  The main table containing all the file name, ID,
location, size, 
submitter, category, comment links, ratings, evaluations, alt text, etc.
fields.

(2) Last Update.  A very small (1 record - 1 field) table containing a
timestamp indicating 
the last time the photo table was updated. [Add a routine when you add
or delete a photo 
to change the date/time here.]

(3) Enumeration Table.  A two field table containing an ID link back
into the photo table 
and a 'counter' field numbered '1' thru 'x' that you match your random
number into.  
[Create this table with a routine that (a) wipes this table, and (b)
loops through the 
photo table writing ID and 'counter' here one record at a time to ensure
you get your '1' 
thru 'x' coverage.]  {The tricky part is 'when' to write this table.
Hint: That's what 
the two date tables are for.}

(4) Last Enumeration Date.  Another very small (1 record - 1 field)
table containing a 
timestamp indicating the last time the enumeration table was updated.
[You see where I'm 
going here, right?]


Now, every time you update the photo table you update the 'last update'
timestamp so 
you have a record telling you when the last change to the table was
made.

Sooooo ... when somebody hits your page/link/whatever and wants to see a
random photo 
you:

(i) Check to see if the last enumeration time is 'after' the last update
to the photo 
database.  If it is, everything is cool - you select your random number,
match through 
the enumeration table to get the photo table id and use that to link
into the photo 
table and pull up the snazzy pix and knock their socks off.

(ii) Otherwise - you add a step to kick off the routine to rewrite the
enumeration 
table and update the last enumeration date.  Now that things are up to
date again, you 
pull your random number between '1' and 'select max(counter) from
enumeration_table' 
and go get that picture.



Probably not the most 'efficient' design and may or may not scale well
to enormous 
quantities of photos being updated every six tenths of a second ... But
for a small 
to midsized app this might work 'well enough'.


HTH,

RonL.