title: MySQL: the inconsistency of auto increment in id numbers
body: <p>I wonder if you have come across this that the id numbers of auto increment don't arrange in correct order <strong>when you click on Browse button on phpMyAdmin</strong> - is it just me? Something I have set incorrectly in the db table? </p>
  
  <p>For instance, when you insert a series of data into a table and you have deleted some of them as well, so when you check this table on phpMyAdmin, these data should appear in an order like this,</p>
  
  <pre><code>id
  
  2
  24
  28
  296
  300
  </code></pre>
  
  <p>but in most of my tables they don't appear in orders, instead they appear in something like this,</p>
  
  <pre><code>id
  
  24
  300    
  2
  296    
  28
  </code></pre>
  
  <p>as in this picture below,</p>
  
  <p><img src="https://i.stack.imgur.com/Q7Fvz.jpg" alt="alt text"></p>
  
  <p>Can we do something about it so that the IDs appear in the correct order?</p>
  
  <p><strong>EDIT 1:</strong></p>
  
  <p>I think most of you have misunderstood the issue I raided here. I mean when you click on Browser button on the phpMyAdmin to list all the data in your tables - not when you use SQL query to list the output by using <code>order by</code>. </p>
  
  <p>Does it make sense?</p>
  
  <p><img src="https://i.stack.imgur.com/oBRV5.jpg" alt="alt text"></p>
  
  <p><strong>EDIT 2:</strong></p>
  
  <p>This is one of my table structure - does it help you to see what is wrong in it?</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `root_pages` (
    `pg_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `pg_url` varchar(255) DEFAULT NULL,
    `pg_title` varchar(255) DEFAULT NULL,
    `pg_subtitle` varchar(255) DEFAULT NULL,
    `pg_description` text,
    `pg_introduction` text,
    `pg_content_1` text,
    `pg_content_2` text,
    `pg_content_3` text,
    `pg_content_4` text,
    `pg_order` varchar(255) DEFAULT NULL,
    `pg_hide` varchar(255) DEFAULT '0',
    `pg_highlight` varchar(255) DEFAULT '0',
    `pg_important` varchar(255) DEFAULT '0',
    `pg_parent` varchar(255) DEFAULT '0',
    `parent_id` varchar(255) DEFAULT NULL,
    `pg_cat_id` varchar(255) DEFAULT NULL COMMENT 'page category id',
    `ps_cat_id` varchar(255) DEFAULT NULL COMMENT 'post category id',
    `tmp_id` varchar(255) DEFAULT NULL COMMENT 'template id',
    `pg_backdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `pg_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `pg_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`pg_id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  </code></pre>