Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #15192

Re: Problem creating a series of tables

Show all headers | View raw

Thomas 'PointedEars' Lahn wrote:

> ,----
> | mysql> create temporary table tmp (id int(10) primary key not null);
> | Query OK, 0 rows affected (0.01 sec)
> | 
> | mysql> explain tmp;
> | +-------+---------+------+-----+---------+-------+
> | | Field | Type    | Null | Key | Default | Extra |
> | +-------+---------+------+-----+---------+-------+
> | | id    | int(10) | NO   | PRI | NULL    |       |
> | +-------+---------+------+-----+---------+-------+
> | 1 row in set (0.01 sec)
> | 
> | mysql> insert into tmp values (1);
> | Query OK, 1 row affected (0.00 sec)
> | 
> | mysql> insert into tmp values (0);
> | Query OK, 1 row affected (0.01 sec)
> | 
> | mysql> select * from tmp;
> | +----+
> | | id |
> | +----+
> | |  0 |
> | |  1 |
> | +----+
> | 2 rows in set (0.00 sec)
> `----

I realize that this is not a good test.  The order could still be arbitrary; 
one run is insufficient to show the opposite.  But by the law of large 
numbers, if the order of result records would be arbitrary, the record “1” 
should occur at first position about 50 % of the time, and there should be 
at least one occurrence of “1” at first position.  But:

$ mysql -u root -p$pass <<<'create database tmp; use tmp; create table tmp 
(id int(10) primary key not null); insert into tmp values (1); insert into 
tmp values (0);'

$ for i in $(seq 1 100); do mysql -u root -p$pass <<<'use tmp; select id 
from tmp limit 1\G'; done | grep '^id: 0' | wc -l
100

$ for i in $(seq 1 1000); do mysql -u root -p$pass <<<'use tmp; select id 
from tmp limit 1\G'; done | grep '^id: 1' | wc -l
0

$ for i in $(seq 1 10000); do mysql -u root -p$pass <<<'use tmp; select id 
from tmp limit 1\G'; done | grep '^id: 1' | wc -l
0

$ for i in $(seq 1 100000); do mysql -u root -p$pass <<<'use tmp; select 
id from tmp limit 1\G'; done | grep '^id: 1' | wc -l
0

You can repeat this with ever larger numbers; the results are consistent: 
Never once is “1” at first position.  Because “0” always is, although that 
record had been inserted *after* “1”.  Conclusion: The result records are 
sorted by primary key.  Q.E.D.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Problem creating a series of tables richard <noreply@example.com> - 2015-03-31 12:14 -0400
  Re: Problem creating a series of tables Paul Herber <paul@pherber.com> - 2015-03-31 19:09 +0100
    Re: Problem creating a series of tables richard <noreply@example.com> - 2015-03-31 14:30 -0400
  Re: Problem creating a series of tables Denis McMahon <denismfmcmahon@gmail.com> - 2015-03-31 19:40 +0000
    Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-03-31 16:19 -0400
      Re: Problem creating a series of tables Richard Yates <richard@yatesguitar.com> - 2015-03-31 18:07 -0700
        Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-03-31 21:23 -0400
      Re: Problem creating a series of tables Erwin Moller <erwinmollerusenet@xs4all.nl> - 2015-04-01 12:19 +0200
        Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-02 15:23 +0200
          Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 10:17 -0400
    Re: Problem creating a series of tables richard <noreply@example.com> - 2015-03-31 20:14 -0400
      Re: Problem creating a series of tables Doug Miller <doug_at_milmac_dot_com@example.com> - 2015-04-01 02:44 +0000
        Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-03-31 23:06 -0400
          Re: Problem creating a series of tables richard <noreply@example.com> - 2015-04-01 00:09 -0400
            Re: Problem creating a series of tables Doug Miller <doug_at_milmac_dot_com@example.com> - 2015-04-01 20:37 +0000
              Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-01 17:08 -0400
    Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-02 14:50 +0200
      Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 10:20 -0400
        Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-02 22:32 +0200
          Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-03 00:44 +0200
            Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 19:22 -0400
            Re: Problem creating a series of tables Jerry Stuckle <jstucklex@attglobal.net> - 2015-04-02 19:24 -0400
            Re: Problem creating a series of tables "Christoph M. Becker" <cmbecker69@arcor.de> - 2015-04-03 01:46 +0200
              Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-03 20:45 +0200
          Re: Problem creating a series of tables Richard Yates <richard@yatesguitar.com> - 2015-04-02 15:59 -0700
            Re: Problem creating a series of tables Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-04-03 01:10 +0200
              Re: Problem creating a series of tables Richard Yates <richard@yatesguitar.com> - 2015-04-02 18:44 -0700

csiph-web