SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL: Resultset Randomly listed

    HI

    I have a simple table in my database called 'avatars'

    Code:
    CREATE TABLE `avatars` (
      `avatar_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `avatar_image_name` VARCHAR(255) DEFAULT NULL,
      PRIMARY KEY (`avatar_id`)
    ) ENGINE=MYISAM AUTO_INCREMENT=60 DEFAULT CHARSET=latin1
    Which has some data in it.

    When I run a simple select query:

    Code:
    SELECT * 
    FROM  `avatars` 
    LIMIT 0 , 30
    What order does it follow to show the resultset? As u can see from the attached screenshot that the autoincrement id column is not in serial order.

    Please explain this.


    Selection_001.png

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    Please explain this.
    it's really simple

    without an ORDER BY clause, the database engine is allowed to return rows in whatever sequence it wishes
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it's really simple

    without an ORDER BY clause, the database engine is allowed to return rows in whatever sequence it wishes
    I know but what I want to know is that does it have any internal logic at all?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    ... does it have any internal logic at all?
    it returns them in the sequence it finds them
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it returns them in the sequence it finds them
    But logically should not it find the records in the sequence it was inserted?

    Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, because the insertion takes place on the most readily available disc space, which could be anywhere in the sequence
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    725
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, because the insertion takes place on the most readily available disc space, which could be anywhere in the sequence
    Thats strage!!

    Where did you find this info sir? coz I was unble to see any such info in the official document

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by cancer10 View Post
    ...I was unble to see any such info in the official document
    you're gonna have an even harder time finding anything that contradicts it

    r937.com | rudy.ca | 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
  •