SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    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,329
    Mentioned
    63 Post(s)
    Tagged
    3 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    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,329
    Mentioned
    63 Post(s)
    Tagged
    3 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    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,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, because the insertion takes place on the most readily available disc space, which could be anywhere in the sequence
    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
  •