SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post [PHP+MySQL] Holes in primary key auto_increment column

    Hi,

    Right now the website is to randomly pick a row with a "ID" number from the table, the number is chosen via php. The problem is there can be holes within the numbers due to deletion of entries over a period of time.

    So far I had the table to drop the ID field and re-add it as auto_increment primary key to recount the numbers every time some entry is deleted. However the number of entries in row has surpassed 10000 and the slowness is notable.

    recently few people even accessed pages while its recounting even, its not quite efficient. Is there anyway for mysql to easily recount and order the numbers accordingly?

    By holes i mean:

    Code:
     1     NAME     EMAIL     ETC
     2     NAME     EMAIL     ETC
     4     NAME     EMAIL     ETC
     8     NAME     EMAIL     ETC
    90    NAME     EMAIL     ETC
    91    NAME     EMAIL     ETC
    92    NAME     EMAIL     ETC
    93    NAME     EMAIL     ETC
    94    NAME     EMAIL     ETC
    .
    .
    .
    What if say the random number is 5, but there is no 5. Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    there is nothing wrong with holes

    the purpose of an auto_increment column is to provide uniqueness, nothing more

    if the random number generated is 5, but there is no row with id=5, then you can do several things -- generate another random number until you get a hit, retrieve the first row with an id that is greater than 5, or generate the random number on some other column

    but re-sequencing the column is a poor solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so you are suggesting its just better to choose a random number and loop it through while until i get a random one that does exist? ^_^'

    That is okay, but in situations where I need 6 -10 random numbers and their corresponding 6-10 rows that's going to be a lot of while loops and queries.... is that really much efficient than resorting the ID field?

    Its worth a try for sure but it would have been nice if it could be resorted though. Thanks r937.

    I'll wait it out to see if there are other suggestions as well.

  4. #4
    SitePoint Zealot
    Join Date
    Oct 2005
    Posts
    128
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right now was trying to use "WHERE `ID`='$rand_num'.. but holes in sequence are the problem. The main thing is i'm avoiding order by RAND()... So I was trying on my thinking cap and got an idea. Wouldn't the following work out for me without any load on the server?

    Code:
    SELECT * FROM `TABLE` WHERE `ETC`='something' LIMIT $rand_num, 1
    I mean the LIMIT row, entries.. the row doesn't matter if its sequenced or not, it doesn't change does it?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    WHERE id >= $rand LIMIT 1
    since id is the primary key, it is indexed, and therefore this query is efficient

    note that this will not appear very random if there are ~huge~ gaps -- but normal deletions are okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •