Interesting Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GPU Free Documentation License) 
Last Updated: Mon Nov 22 20:01:59 EST 2004

The latest version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download





TIP 1:

    Find out who is doing what, and kill the process if needed. 
    This example kills Id 657.

    mysql> show processlist;                                                             
    show processlist;                                                                    
    +-----+------+-----------+---------+---------+-------+-------+------------------+    
    | Id  | User | Host      | db      | Command | Time  | State | Info             |    
    +-----+------+-----------+---------+---------+-------+-------+------------------+    
    | 657 | prog | localhost | weather | Sleep   | 28619 |       | NULL             |    
    | 782 | prog | localhost | weather | Sleep   |   853 |       | NULL             |    
    | 785 | prog | localhost | NULL    | Query   |     0 | NULL  | show processlist |    
    +-----+------+-----------+---------+---------+-------+-------+------------------+    
    3 rows in set (0.00 sec)                                                             
                                                                                         
    mysql>kill 657                                                                               
    
    Or, from the command line, to kill process 782

    [root@third-fl-71 mysql]# mysqladmin processlist                                    
    +-----+------+-----------+---------+---------+------+-------+------------------+    
    | Id  | User | Host      | db      | Command | Time | State | Info             |    
    +-----+------+-----------+---------+---------+------+-------+------------------+    
    | 782 | prog | localhost | weather | Sleep   | 2676 |       |                  |    
    | 785 | prog | localhost |         | Sleep   | 1823 |       |                  |    
    | 793 | root | localhost |         | Query   | 0    |       | show processlist |    
    +-----+------+-----------+---------+---------+------+-------+------------------+    
    [root@third-fl-71 mysql]#                                                           
                              
    [root@third-fl-71 mysql]# mysqladmin kill 782                                                          

    Note, the following can also be helpful

    mysql> show status;
        or
    mysql> show status\G
        also
    mysql> show innodb status;
        or 
    mysql> show table status like '%';

        The above gives you create time and other information. 

    

 

TIP 2:

    Clean up binary log files.  For a default install they may be in 
          /usr/local/var/ 
    with names ending in -bin.000001,-bin.000002,..

    
    mysql> reset master;
    reset master;
    Query OK, 0 rows affected (0.02 sec)

    See (TIP 24:) details working with binary log files and (TIP 25:) explains
    how to setup logging.



TIP 3:

    Can the order of the columns in a create statement make a difference? YES

       create table t (
          a int,
          b int,
          timeUpdate timestamp,
          timeEnter timestamp );

    The first timestamp will always be the "automatically generated" time. So
    if the record is updated, or inserted, this time gets changed. If the 
    order is changed, "timeEnter" is before "timeUpdate", then,  "timeEnter"
    would get updated.  First timestamp column updates automatically.

    Note, in the table above timeEnter will only get updated if passed a null
    value.

         insert into t (a,b,timeEnter) values (1,2,NULL);

    Hints: Need mm-dd-yyyy hh:mm:ss format?

      select a,b,DATE_FORMAT(timeUpdate,'%m-%d-%Y %T'),DATE_FORMAT(timeEnter,'%m-%d-%Y %T') from t; 
      +------+------+---------------------------------------+--------------------------------------+   
      | a    | b    | DATE_FORMAT(timeUpdate,'%m-%d-%Y %T') | DATE_FORMAT(timeEnter,'%m-%d-%Y %T') |
      +------+------+---------------------------------------+--------------------------------------+
      |    3 |    2 | 04-15-2004 19:14:36                   | 04-15-2004 19:15:07                  |
      |    3 |    2 | 04-15-2004 19:14:39                   | 04-15-2004 19:15:07                  |
      |    5 |    5 | 00-00-0000 00:00:00                   | 04-15-2004 19:15:53                  |
      |    1 |    2 | 00-00-0000 00:00:00                   | 04-15-2004 19:20:15                  |
      +------+------+---------------------------------------+--------------------------------------+         
      4 rows in set (0.00 sec)                                                                                                    



TIP 4:

   Connect, create table and select with Perl (Linux).  First the DBI module is needed, which
   can be installed from the system prompt as follows:

       # perl -MCPAN -e shell
       cpan> install DBI
       cpan> install DBD::mysql

   The following is an example program:

    
          #! /usr/bin/perl -w                                                                                                 
          #  Copyright (GPL) Mike Chirico mchirico@users.sourceforge.net                                                  
          #                                                                                                               
          #  Program does the following:                                                                                  
          #     o connects to mysql                                                                                       
          #     o creates perlTest if it doesn't exist                                                                    
          #     o inserts records                                                                                         
          #     o selects and displays records                                                                            
          #                                                                                                               
          #  This program assumes DBI                                                                                     
          #                                                                                                               
          #  perl -MCPAN -e shell                                                                                         
          #  cpan> install DBI                                                                                            
          #  cpan> install DBD::mysql                                                                                     
          #                                                                                                               
          #                                                                                                               
          #                                                                                                               
          #                                                                                                               
          #                                                                                                               
                                                                                                                          
          use strict;                                                                                                     
          use DBI;                                                                                                        
                                                                                                                          
          #  You will need to change the following:                                                                       
          #     o database                                                                                                
          #     o user                                                                                                    
          #     o password                                                                                                
          my $database="yourdatabase";                                                                                    
          my $user="user1";                                                                                               
          my $passwd="hidden";                                                                                            
          my $count = 0;                                                                                                  
          my $tblcreate= "                                                                                                
            CREATE TABLE IF NOT EXISTS perlTest (                                                                         
              pkey int(11) NOT NULL auto_increment,                                                                       
              a int,                                                                                                      
              b int,                                                                                                      
              c int,                                                                                                      
              timeEnter timestamp(14),                                                                                    
             PRIMARY KEY  (pkey)                                                                                          
                                                                                                                          
             ) ";                                                                                                                 
                                                                                                                          
          my $insert= "                                                                                                   
              insert into perlTest (a,b,c)                                                                                
               values (1,2,3),(4,5,6),(7,8,9)";                                                                           
                                                                                                                          
          my $select="                                                                                                    
              select a,b,c from perlTest ";                                                                               
                                                                                                                          
                                                                                                                          
                                                                                                                          
          my $dsn = "DBI:mysql:host=localhost;database=${database}";                                                      
          my $dbh = DBI->connect ($dsn, $user, $passwd)                                                                   
              or die "Cannot connect to server\n";                                                                        
                                                                                                                          
                                                                                                                          
                                                                                                                          
          my $s = $dbh->prepare($tblcreate);                                                                              
             $s->execute();                                                                                               
             $s = $dbh->prepare($insert);                                                                                 
             $s->execute();                                                                                               
                                                                                                                          
                                                                                                                          
                                                                                                                          
             $s = $dbh->prepare($select);                                                                                 
             $s->execute();                                                                                               
                                                                                                                          
                                                                                                                          
          while(my @val = $s->fetchrow_array())                                                                           
          {                                                                                                               
              print " $val[0]  $val[1]  $val[2]\n";                                                                       
              ++$count;                                                                                                   
          }                                                                                                               
          $s->finish();                                                                                                   
                                                                                                                          
          $dbh->disconnect ( );                                                                                           
                                                                                                                          
          exit (0);         



TIP 5:

     Remove duplicate entries.  Assume the following table and data.
    
              CREATE TABLE IF NOT EXISTS dupTest (          
                pkey int(11) NOT NULL auto_increment,        
                a int,                                       
                b int,                                       
                c int,                                       
                timeEnter timestamp(14),                     
               PRIMARY KEY  (pkey)                           
                                                             
               );

               insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
                  (1,5,4),(1,6,4);



       mysql> select * from dupTest;                            
       select * from dupTest;                                                                                                    
       +------+------+------+------+---------------------+                
       | pkey | a    | b    | c    | timeEnter           |                
       +------+------+------+------+---------------------+      
       |    1 |    1 |    2 |    3 | 2004-04-16 10:55:35 |      
       |    2 |    1 |    2 |    3 | 2004-04-16 10:55:35 |      
       |    3 |    1 |    5 |    4 | 2004-04-16 10:55:35 |      
       |    4 |    1 |    6 |    4 | 2004-04-16 10:55:35 |      
       +------+------+------+------+---------------------+      
       4 rows in set (0.00 sec)                                 
                                                                
       mysql>                                                   
    
       Note, the first two rows contains duplicates in columns a and b. It contains
       other duplicates; but, leaves the other duplicates alone.
    
          mysql> ALTER IGNORE TABLE  dupTest ADD UNIQUE INDEX(a,b);

          mysql> select * from dupTest;                                   
          select * from dupTest;                                          
          +------+------+------+------+---------------------+             
          | pkey | a    | b    | c    | timeEnter           |             
          +------+------+------+------+---------------------+             
          |    1 |    1 |    2 |    3 | 2004-04-16 11:11:42 |             
          |    3 |    1 |    5 |    4 | 2004-04-16 11:11:42 |             
          |    4 |    1 |    6 |    4 | 2004-04-16 11:11:42 |             
          +------+------+------+------+---------------------+             
          3 rows in set (0.00 sec)                                        
                                                                          
          

TIP 6:

     Show status information on a table. Note, if the database was started
     with --safe-show-database or --skip-show-database some of these commands
     may not work. Note the "\G" option may provide a nicer format.

     Show the create statement:
                                                                                      
               mysql> show create table dupTest\G                                     
               show create table dupTest\G                                            
               *************************** 1. row ***************************         
                      Table: dupTest                                                  
               Create Table: CREATE TABLE `dupTest` (                                 
                 `pkey` int(11) NOT NULL auto_increment,                              
                 `a` int(11) default NULL,                                            
                 `b` int(11) default NULL,                                            
                 `c` int(11) default NULL,                                            
                 `timeEnter` timestamp NOT NULL,                                      
                 PRIMARY KEY  (`pkey`),                                               
                 UNIQUE KEY `a` (`a`,`b`)                                             
               ) TYPE=MyISAM DEFAULT CHARSET=latin1                                   
               1 row in set (0.00 sec)                                                
                                                                                      

TIP 7:

     Transactions: Not all table types support transactions. BDB and INNODB type do support transactions. 
     Assuming the server has NOT been started with --skip-bdb or --skip-innodb the following should work:

          mysql> create table tran_test (a int, b int) type = InnoDB;
          mysql> begin;
          mysql> insert into tran_test (a,b) values (1,2);
                                                  
          mysql> select * from tran_test;         
          select * from tran_test;                
          +------+------+                         
          | a    | b    |                         
          +------+------+                         
          |    1 |    2 |                         
          +------+------+                         
          1 row in set (0.00 sec)                 

          mysql> rollback;

          mysql> select * from tran_test;  
          select * from tran_test;         
          Empty set (0.00 sec)             

 
     Summary: rollback undoes everything and commit will save.



TIP 8:

     MERGE:  Several tables can be merged into one.

        CREATE TABLE log_01 (
           pkey int(11) NOT NULL auto_increment,
           a int,
           b varchar(12),
           timeEnter timestamp(14),
           PRIMARY KEY  (pkey)
         ) type=MyISAM;

        CREATE TABLE log_02 (
           pkey int(11) NOT NULL auto_increment,
           a int,
           b varchar(12),
           timeEnter timestamp(14),
           PRIMARY KEY  (pkey)
         ) type=MyISAM;

        CREATE TABLE log_summary (
           pkey int(11) NOT NULL auto_increment,
           a int,
           b varchar(12),
           timeEnter timestamp(14),
           PRIMARY KEY  (pkey)
         ) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;

         mysql> insert into log_01 (a,b) values (1,'log1');
         mysql> insert into log_02 (a,b) values (1,'log2');

         mysql> select * from log_summary;                    
         select * from log_summary;                           
         +------+------+------+---------------------+         
         | pkey | a    | b    | timeEnter           |         
         +------+------+------+---------------------+         
         |    1 |    1 | log1 | 2004-04-16 11:59:55 |         
         |    1 |    1 | log2 | 2004-04-16 12:00:08 |         
         +------+------+------+---------------------+         
         2 rows in set (0.00 sec)                             

         Reference:
         http://dev.mysql.com/doc/mysql/en/MERGE.html
                                                              


TIP 9:

     Updating foreign keys in a multiuser environment. Using LAST_INSERT_ID().
     Also see (TIP 29) after reading this.

     The LAST_INSERT_ID() is unique to the login session. This allows updating
     of foreign keys.

        CREATE TABLE keytest (
           pkey int(11) NOT NULL auto_increment,
           ptext text,
           ptype int(11),
           timeEnter timestamp(14),
           PRIMARY KEY  (pkey)
   
        );
 
        CREATE TABLE foreignkeytest (
            pkey int(11) NOT NULL auto_increment,
            pkeykeytest int(11) NOT NULL,
            ptext text,
            ptype int(11),
            timeEnter timestamp(14),
            PRIMARY KEY  (pkey)
   
        );

        mysql> insert into keytest(ptext,ptype) values ('one',1);

        mysql> select LAST_INSERT_ID() from keytest;                
        select LAST_INSERT_ID() from keytest;                       
        +------------------+                                        
        | last_insert_id() |                                        
        +------------------+                                        
        |                1 |                                        
        +------------------+                                        
        1 row in set (0.03 sec)                                     
                                                                    
       mysql> insert into foreignkeytest (ptext,pkeykeytest) values ('one',LAST_INSERT_ID());

       Note: If your session didn't update any records, LAST_INSERT_ID() will be zero.  Never
       assume LAST_INSERT_ID()+1 will be the next record.  If another session inserts a record,
       this value may be taken. You are assured that this value will be unique to the "session".

       **SPECIAL NOTE:  MySQL 4.1.2. supports UUID.
             
             mysql> select uuid();                     
	     select uuid();                            
	     +--------------------------------------+  
	     | uuid()                               |  
	     +--------------------------------------+  
	     | 167c1afe-0a0f-1027-891e-0004e222b485 |  
	     +--------------------------------------+  
	     1 row in set (0.00 sec)                   

          "A UUID is designed as a number that is globally unique in space and time. 
           Two calls to UUID() are expected to generate two different values, even if 
           these calls are performed on two separate computers that are not 
           connected to each other."

       So in the future, UUID() could be used as a better primary key. The advantage
       being tables from a one server could be up-loaded to a second server without
       worrying about duplicate keys.    

                                                                                                 
                                                                                                                      
TIP 10:

      Monitor port 3306:

        tcpdump  -i eth0 -nN -vvv -xX  -s 1500  port 3306 and host not 192.168.1.102

      The -s is length of each packet. This monitors all traffic on port 3306 excluding
      the good client 192.168.1.102.  The out will be in the following format:


[root@third-fl-71 root]# tcpdump  -i eth0 -nN -vvv -xX  -s 1500  port 3306 and host not 192.168.1.102
tcpdump: listening on eth0
13:05:01.857705 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok] 2542149285:2542149285(0) win 16384 <mss 1460,nop,nop,sackO
0x0000   4500 0030 0b39 4000 8006 6b90 c0a8 0167        E..0.9@...k....g
0x0010   c0a8 0147 1023 0cea 9786 1ea5 0000 0000        ...G.#..........
0x0020   7002 4000 ebe7 0000 0204 05b4 0101 0402        p.@.............
13:05:04.863552 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok] 2542149285:2542149285(0) win 16384 <mss 1460,nop,nop,sackO



TIP 11:

     Create a C or C++ API

     Download the following:

       http://prdownloads.sourceforge.net/souptonuts/mysql_select-0.0.2.tar.gz?download

     Also reference:
       http://dev.mysql.com/downloads/other/plusplus/



TIP 12:

     Connect and Select from Java

     //                                                                                                                           
     //   mysql-connector-java-3.0.11-stable-bin.jar or later must be downloaded                                                  
     //   and installed from:                                                                                                     
     //      http://dev.mysql.com/downloads/connector/j/3.0.html                                                                  
     //                                                                                                                           
     //   Edit to include your username and password:                                                                             
     //      String userName = "yourusername";                                                                                    
     //      String password = "passord";                                                                                         
     //                                                                                                                           
     //                                                                                                                           
     //   Compile:                                                                                                                
     //     javac Select.java                                                                                                     
     //      (or you can also use GCC's gcj  "gcj -C Select.java")                                                                
     //      http://gcc.gnu.org/onlinedocs/gcj/
     //      http://www.linuxjournal.com/article.php?sid=4860
     //      http://gcc.gnu.org/java/papers/cni/t1.html
     //                                                                                                                           
     //   Run:                                                                                                                    
     //     java Select                                                                                                           
     //                                                                                                                           
     //                                                                                                                           
     //   The program above assumes "exams" has been created in                                                                   
     //   the "test" database. If you create "exams" change the                                                                   
     //   select statment to read:                                                                                                
     //       SELECT pkey,name,exam,score FROM yourdatabase.exams                                                                 
     //                                                                                                                           
     //                                                                                                                           
     //         CREATE TABLE exams (                                                                                              
     //           pkey int(11) NOT NULL auto_increment,                                                                           
     //           name varchar(15),                                                                                               
     //           exam int,                                                                                                       
     //           score int,                                                                                                      
     //           PRIMARY KEY  (pkey)                                                                                             
     //                                                                                                                           
     //         );                                                                                                                
     //                                                                                                                           
     //         insert into exams (name,exam,score) values ('Bob',1,75);                                                          
     //         insert into exams (name,exam,score) values ('Bob',2,77);                                                          
     //         insert into exams (name,exam,score) values ('Bob',3,78);                                                          
     //         insert into exams (name,exam,score) values ('Bob',4,80);                                                          
     //                                                                                                                           
     //         insert into exams (name,exam,score) values ('Sue',1,90);                                                          
     //         insert into exams (name,exam,score) values ('Sue',2,97);                                                          
     //         insert into exams (name,exam,score) values ('Sue',3,98);                                                          
     //         insert into exams (name,exam,score) values ('Sue',4,99);                                                          
     //                                                                                                                           
                                                                                                                                  
     import java.sql.*;                                                                                                           
                                                                                                                                  
     public class Select                                                                                                          
         {                                                                                                                        
                                                                                                                                  
       public static void main (String[ ] args)                                                                                   
         {                                                                                                                        
           Connection conn = null;                                                                                                
           String url = "jdbc:mysql://localhost/";                                                                                
           String userName = "username1";                                                                                         
           String password = "password1";                                                                                         
                                                                                                                                  
           try                                                                                                                    
            {                                                                                                                     
            Class.forName ("com.mysql.jdbc.Driver").newInstance ( );                                                              
            conn = DriverManager.getConnection (url, userName, password);                                                         
            // System.out.println ("Connected");                                                                                  
                                                                                                                                  
            Statement s = conn.createStatement ( );                                                                               
            s.executeQuery ("SELECT pkey,name,exam,score FROM test.exams");                                                       
            ResultSet rs = s.getResultSet ( );                                                                                    
            int count = 0;                                                                                                        
               while (rs.next ( ))  // loop through rows of result set                                                            
                {                                                                                                                 
                 int pkey = rs.getInt (1);                                                                                        
                 String name = rs.getString(2);                                                                                   
                 int exam = rs.getInt(3);                                                                                         
                 int score = rs.getInt(4);                                                                                        
                 ++count;                                                                                                         
                 System.out.println (count + ",inum: " + pkey + ",name: " + 
                   name + ",exam: " + exam + ",score: " + score );               
                }                                                                                                                 
              rs.close ( );    // close result set                                                                                
              s.close ( );     // close statement                                                                                 
              System.out.println (count + " rows were returned");                                                                 
                                                                                                                                  
           }                                                                                                                      
           catch (Exception e)                                                                                                    
             {                                                                                                                    
              System.err.println ("Cannot connect to server"+e);                                                                  
             }                                                                                                                    
          finally                                                                                                                 
           {                                                                                                                      
              if (conn != null)                                                                                                   
               {                                                                                                                  
                try                                                                                                               
                 {                                                                                                                
                   conn.close ( );                                                                                                
                // System.out.println ("Disconnected");  /* for debugging */                                                      
                 }                                                                                                                
               catch (Exception e) { /* ignore close errors */ }                                                                  
              }                                                                                                                   
           }                                                                                                                      
       }                                                                                                                          
                                                                                                                                  
   }                                                                                                                              
                                                                                                                                  
                                                                                                                                  

TIP 13:

     Print defaults for the current client connection


            $ /usr/local/bin/my_print_defaults client mysql         
            --port=3306                                             
            --socket=/tmp/mysql.sock                                
            --no-auto-rehash                                        
                                                                    


TIP 14:

     Quick Status:
                                                                                                
     mysql> \s                                                                                                                    
     --------------                                                                             
     /usr/local/bin/mysql  Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)                    
                                                                                                
     Connection id:          642                                                                
     Current database:                                                                          
     Current user:           prog@localhost                                                     
     SSL:                    Not in use                                                         
     Current pager:          stdout                                                             
     Using outfile:          ''                                                                 
     Using delimiter:        ;                                                                  
     Server version:         4.1.1-alpha-log                                                    
     Protocol version:       10                                                                 
     Connection:             Localhost via UNIX socket                                          
     Client characterset:    latin1_swedish_ci                                                  
     Server characterset:    latin1_swedish_ci                                                  
     UNIX socket:            /tmp/mysql.sock                                                    
     Uptime:                 1 day 15 hours 24 min 38 sec                                       
                                                                                                
     Threads: 1  Questions: 4896  Slow queries: 0                                               
     Opens: 14  Flush tables: 1  Open tables: 8  Queries per second avg: 0.035                  
     --------------                                                                             
                                                                                                


TIP 15:

     "error: 1016: Can't open file:" If it's from an orphaned file, not in the database
     but on disk, then, the disk file may need to be deleted.

     myisamchk can help with damaged files.  It's best to stop the database.

         # su -
         # mysqladmin shutdown

         # cd /usr/local/var/database
         # myisamchk *
     
         # /etc/init.d/mysql restart



TIP 16:

     Finding records that do not match between two tables.

          CREATE TABLE bookreport (                                                                    
            b_id int(11) NOT NULL auto_increment,                                                                                 
            s_id int(11) NOT NULL,                                                                                                
            report varchar(50),                                                                        
            PRIMARY KEY  (b_id)                                                                        
                                                                                                       
          );                                                                                           
                                                                                                       
          CREATE TABLE student (                                                                       
            s_id int(11) NOT NULL auto_increment,                                                      
            name varchar(15),                                                                          
            PRIMARY KEY  (s_id)                                                                        
          );                                                                                           
                                                                                                       
                                                                                                       
          insert into student (name) values ('bob');                                                   
          insert into bookreport (s_id,report)                                                         
            values ( last_insert_id(),'A Death in the Family');                                        
                                                                                                       
          insert into student (name) values ('sue');                                                   
          insert into bookreport (s_id,report)                                                         
            values ( last_insert_id(),'Go Tell It On the Mountain');                                   
                                                                                                       
          insert into student (name) values ('doug');                                                  
          insert into bookreport (s_id,report)                                                         
            values ( last_insert_id(),'The Red Badge of Courage');                                     
                                                                                                       
          insert into student (name) values ('tom');                                                   
                                                                                                       
              
     To find the sudents where are missing reports:
                                                                                         
          select s.name from student s                                                                 
            left outer join bookreport b on s.s_id = b.s_id                                            
          where b.s_id is null;                                                                        

              +------+                
              | name |                
              +------+                
              | tom  |                
              +------+                
              1 row in set (0.00 sec) 
                                      

     Ok, next suppose there is an orphan record in    
     in bookreport. First delete a matching record
     in student:

       delete from student where s_id in (select max(s_id) from bookreport);

     Now, how to find which one is orphaned:

       select * from bookreport b left outer join  
       student s on b.s_id=s.s_id where s.s_id is null;

     +------+------+--------------------------+------+------+      
     | b_id | s_id | report                   | s_id | name |      
     +------+------+--------------------------+------+------+      
     |    4 |    4 | The Red Badge of Courage | NULL | NULL |      
     +------+------+--------------------------+------+------+      
     1 row in set (0.00 sec)                                       

     To clean things up (Note in 4.1 you can't do subquery on 
     same table in a delete so it has to be done in 2 steps):

       select @t_sid:=b.s_id from bookreport b left outer join  
         student s on b.s_id=s.s_id where s.s_id is null;

       delete from student where s_id=@t_sid;


     But, functions do work in delete.  For instance the 
     following is possible:

        delete from student where s_id=max(s_id);


     It just a problem when joining the table where the
     delete will occur with another table. Another 
     option is two create a second temp table and
     locking the first one.



TIP 17:                                                                                                

     Getting a random roll of the dice:

          CREATE TABLE dice (                                           
            d_id int(11) NOT NULL auto_increment,                                                                                 
            roll int,                                                   
            PRIMARY KEY  (d_id)                                         
          );                                                            
                                                                        
          insert into dice (roll) values (1);                           
          insert into dice (roll) values (2);                           
          insert into dice (roll) values (3);                           
          insert into dice (roll) values (4);                           
          insert into dice (roll) values (5);                           
          insert into dice (roll) values (6);                           
                                                                        
                                                                        
          select roll from dice order by rand() limit 1;                

                                                                        

TIP 18:

     Creating and using your own password file.
     This assumes MySQL has been configured with SSL support [ ./configure --with-openssl ]

       CREATE TABLE password (                                                                     
         p_id int(11) NOT NULL auto_increment,                                                     
         id varchar(20),                                                                           
         valid enum('y','n'),                                                                      
         password BLOB,                                                                     
         timeEnter timestamp,                                                                      
         PRIMARY KEY  (p_id)                                                                       
       );                                                                                          
                                                                                                   
                                                                                                   
       insert into password (id,password)                                                          
                values ('bob',des_encrypt('secret','somekey'));                                    
       insert into password (id,password)                                                          
                values ('tom',des_encrypt('password','somekey'));                                  
       insert into password (id,password)                                                          
                values ('kate',des_encrypt('desmark','somekey'));                                  
       insert into password (id,password)                                                          
                values ('tim',des_encrypt('tim','somekey'));                                       
       insert into password (id,password)                                                          
                values ('sue',des_encrypt('SUM','somekey'));                                       
       insert into password (id,password)                                                          
                values ('john',des_encrypt('dotgo86','somekey'));                                  
       insert into password (id)                                                                   
                values ('randal');                                                                 


       mysql>   select id,des_decrypt(password,'somekey') from password;             

       +--------+---------------------------------+                                  
       | id     | des_decrypt(password,'somekey') |                                  
       +--------+---------------------------------+                                  
       | bob    | secret                          |                                  
       | tom    | password                        |                                  
       | kate   | desmark                         |                                  
       | tim    | tim                             |                                  
       | sue    | SUM                             |                                  
       | john   | dotgo86                         |                                  
       | randal | NULL                            |                                  
       +--------+---------------------------------+                                  
       7 rows in set (0.00 sec)                                                      

        Note the bad passwords in the file ('secret','password', and 
        password is the same as the id.
                                                                                                   
        The following update statement will fill in the 
        value for valid:                                                                                                   
                                                                                                   
                                                                                                   
          update password set valid =                                                                 
           COALESCE(                                                                                  
             concat(                                                                                  
               substring('y',1,                                                                      
                1-sign(abs(                                                                          
                  find_in_set( lcase( des_decrypt(password,'somekey'))                              
                   ,concat('password,secret,simple,',lcase(id),',test'))))                         
               ),                                                                             
                                                                                                   
              substring('n',1,                                                                      
                 sign(abs(                                                                         
                   find_in_set( lcase( des_decrypt(password,'somekey'))                            
                   ,concat('password,secret,simple,',lcase(id),',test'))))                         
              )                                                                               
            )                                                                                   
           ,'n');                                                                                       


     Which gives the following:

             mysql> select id,valid from password;          
             select id,valid from password;                 
             +--------+-------+                             
             | id     | valid |                             
             +--------+-------+                             
             | bob    | n     |                             
             | tom    | n     |                             
             | kate   | y     |                             
             | tim    | n     |                             
             | sue    | y     |                             
             | john   | y     |                             
             | randal | n     |                             
             +--------+-------+                             
             7 rows in set (0.00 sec)                       


     To understand it, try taking the following select apart:

        select
           COALESCE(                                                                                  
             concat(                                                                                  
               substring('y',1,                                                                      
                1-sign(abs(                                                                          
                  find_in_set( lcase( des_decrypt(password,'somekey'))                              
                   ,concat('password,secret,simple,',lcase(id),',test'))))                         
               ),                                                                             
                                                                                                   
              substring('n',1,                                                                      
                 sign(abs(                                                                         
                   find_in_set( lcase( des_decrypt(password,'somekey'))                            
                   ,concat('password,secret,simple,',lcase(id),',test'))))                         
              )                                                                               
            )                                                                                   
           ,'n')
           as valid
           from password;                                                                                       
                                                                                                   


TIP 19:

     Order in the count:                                                                                                   

         create table a (a varchar(10));

         insert into a values ('a'),('a'),('b'),('c');

     Note 

        select a,count(a) as count from a group by a order by count;

            +------+-------+        
            | a    | count |        
            +------+-------+        
            | b    |     1 |        
            | c    |     1 |        
            | a    |     2 |        
            +------+-------+        
            3 rows in set (0.00 sec)

     but
                       See above name the function  ------v
       select a,count(a)  from a group by a order by count(a);
              ERROR 1111 (HY000): Invalid use of group function



TIP 20:

     Installation: configured for encryption, C API, and user defined functions.

        ./configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags=-rdynamic


     The --with-openssl is very helpful for creating your own
     password file.  Also, if doing C API, having thread safe
     calls "could" come in handly...it's what I use.

     See (TIP 27) for user defined functions.

     Complete Steps:

     shell> groupadd mysql
     shell> useradd -g mysql mysql
     shell> cd mysql-VERSION
     shell> ./configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags=-rdynamic
     shell> make
     shell> make install
     shell> cp support-files/my-medium.cnf /etc/my.cnf
     shell> cd /usr/local/mysql
     shell> bin/mysql_install_db --user=mysql
     shell> chown -R root  .
     shell> chown -R mysql var
     shell> chgrp -R mysql .
     shell> bin/mysqld_safe --user=mysql &

     See (TIP 25) for configuring the log-bin and log files in /etc/my.cnf

     Installing mysql so that it will startup automatically.
     This also enables it to be restarted, "/etc/init/mysql restart".
    
	 cp ./support-files/mysql.server /etc/init.d/mysql         
	 cd /etc/rc3.d                                             
	 ln -s ../init.d/mysql S85mysql                            
	 ln -s ../init.d/mysql K85mysql                            
	 cd /etc/rc5.d                                             
	 ln -s ../init.d/mysql S85mysql                            
	 ln -s ../init.d/mysql K85mysql                            
	 cd ../init.d                                              
	 chmod 755 mysql                                           
	   

         Deleting any blank users or passwords, and creating a valid
         user is shown below.  

         STEP 1:

           First, connect to the mysql database
           with the connect command:

             mysql> connect mysql;                                                        

         STEP 2:

           Next, delete all blank accounts and/or 
           global host listing:

            mysql> DELETE FROM user WHERE User = '';
            mysql> DELETE FROM db WHERE Host = '%';

         STEP 3:

           Delete any accounts with blank passwords:

            mysql> DELETE FROM user where password='';

         STEP 4:

           Create a valid admin account. The example
           here creates admin1.

            mysql> GRANT ALL PRIVILEGES ON *.* TO admin1@localhost
                   IDENTIFIED BY 's3cr3tpass45' WITH GRANT OPTION;
               
                  or if you want the account to get access from 
                  any host.

            mysql> GRANT ALL PRIVILEGES ON *.* TO admin1@"%"
                   IDENTIFIED BY 's3cr3tpass45' WITH GRANT OPTION;

          STEP 5:

            Restart the server "/etc/init.d/mysql restart"



TIP 21:

     Getting 1 matching row from an OUTER join

     CREATE TABLE parent (                                                                                     
       pkey int(11) NOT NULL auto_increment,                                                                   
       pname varchar(5),                                                                                       
       timeEnter timestamp,                                                                                    
       PRIMARY KEY  (pkey)                                                                                     
                                                                                                               
     );                                                                                                        
                                                                                                               
     CREATE TABLE child (                                                                                      
       ckey int(11) NOT NULL auto_increment,                                                                   
       pkey int(11) NOT NULL,                                                                                  
       cname varchar(5),                                                                                       
       timeEnter timestamp,                                                                                    
       PRIMARY KEY  (ckey)                                                                                     
                                                                                                               
     );                                                                                                        
                                                                                                               
     insert into parent(pname) values ('A');                                                                   
     select @a_lid:=last_insert_id();                                                                          
     insert into child(pkey,cname) values (@a_lid,'a1');                                                       
     insert into child(pkey,cname) values (@a_lid,'a2');                                                       
     insert into child(pkey,cname) values (@a_lid,'a3');                                                       
     insert into parent(pname) values ('B');                                                                   
     select @a_lid:=last_insert_id();                                                                          
     insert into child(pkey,cname) values (@a_lid,'b1');                                                       
     insert into child(pkey,cname) values (@a_lid,'b2');                                                       
     insert into parent(pname) values ('C');                                                                   
                                                                                                               
                                                                                                               
     mysql> select p.*,c.cname,count(c.pkey) as number                                                         
             from parent p left outer join child c on p.pkey=c.pkey                                            
                  where c.pkey is not null group by c.pkey;                                                    
                                                                                                               
     +------+-------+---------------------+-------+--------+                                                   
     | pkey | pname | timeEnter           | cname | number |                                                   
     +------+-------+---------------------+-------+--------+                                                   
     |    5 | A     | 2004-04-28 09:56:59 | a1    |      3 |                                                   
     |    6 | B     | 2004-04-28 09:56:59 | b1    |      2 |                                                   
     +------+-------+---------------------+-------+--------+                                                   
     2 rows in set (0.01 sec)                                                                                  

     For comparison, here is a listing of all the matching data:
                                                                                                         
     mysql> select * from parent p left outer join child c on p.pkey=c.pkey  where c.pkey is not null;                            
     select * from parent p left outer join child c on p.pkey=c.pkey  where c.pkey is not null;               
     +------+-------+---------------------+------+------+-------+---------------------+                       
     | pkey | pname | timeEnter           | ckey | pkey | cname | timeEnter           |                       
     +------+-------+---------------------+------+------+-------+---------------------+                       
     |    5 | A     | 2004-04-28 09:56:59 |    7 |    5 | a1    | 2004-04-28 09:56:59 |                       
     |    5 | A     | 2004-04-28 09:56:59 |    8 |    5 | a2    | 2004-04-28 09:56:59 |                       
     |    5 | A     | 2004-04-28 09:56:59 |    9 |    5 | a3    | 2004-04-28 09:56:59 |                       
     |    6 | B     | 2004-04-28 09:56:59 |   10 |    6 | b1    | 2004-04-28 09:56:59 |                       
     |    6 | B     | 2004-04-28 09:56:59 |   11 |    6 | b2    | 2004-04-28 09:56:59 |                       
     +------+-------+---------------------+------+------+-------+---------------------+                       
     5 rows in set (0.00 sec)                                                                                 



TIP 22:

   Getting a virtual row count.

   Assume the following table:

          CREATE TABLE student (                                                                       
            s_id int(11) NOT NULL auto_increment,                                                      
            name varchar(15),                                                                          
            PRIMARY KEY  (s_id)                                                                        
          );                                                                                           

   Add some rows and delete (just to convice you this works in all cases)

         insert into student (name) values ('tom');
         insert into student (name) values ('bob');
         insert into student (name) values ('sue');
         insert into student (name) values ('mike');
         insert into student (name) values ('joe');
         insert into student (name) values ('zoe');
         insert into student (name) values ('harpo');

         delete from  student where name = 'bob';

   Now, note mc is the row count...independent of s_id;
     

         select a.name,sum(1) as mc  
                from student a, student b 
                where a.s_id <= b.s_id 
                group by a.s_id, a.name  order by mc;              
         +-------+------+                                                                                                         
         | name  | mc   |                                                                                                         
         +-------+------+                                                                                                         
         | harpo |    1 |                                                                                                         
         | zoe   |    2 |                                                                                                         
         | joe   |    3 |                                                                                                         
         | mike  |    4 |                                                                                                         
         | sue   |    5 |                                                                                                         
         | tom   |    6 |                                                                                                         
         +-------+------+                                                                                                         
         6 rows in set (0.00 sec)                                                                                                 
                                                                                                                                  
         mysql>

      *Note: SEE ( TIP 27 ), using the sequence function.



TIP 23:

   Computing running and sliding aggregates. Assume the following table 
   and data:

          CREATE TABLE dice (                                           
            d_id int(11) NOT NULL auto_increment,                                                                                 
            roll int,                                                   
            PRIMARY KEY  (d_id)                                         
          );                                                            
                                                                        
          insert into dice (roll) values (1);                           
          insert into dice (roll) values (2);                           
          insert into dice (roll) values (3);                           
          insert into dice (roll) values (4);                           
          insert into dice (roll) values (5);                           
          insert into dice (roll) values (6);                           


   The following gives a running sum:
                                                                                                                         
	  mysql> select a.roll, sum(b.roll) from dice a, dice b 
                 where b.d_id <= a.d_id group by a.d_id, a.roll;          

	  +------+-------------+                                                                                         
	  | roll | sum(b.roll) |                                                                                         
	  +------+-------------+                                                                                         
	  |    1 |           1 |                                                                                         
	  |    2 |           3 |                                                                                         
	  |    3 |           6 |                                                                                         
	  |    4 |          10 |                                                                                         
	  |    5 |          15 |                                                                                         
	  |    6 |          21 |                                                                                         
	  +------+-------------+                                                                                         
	  6 rows in set (0.00 sec)                                                                                       


   Note, it's also possible to get a running average by changing the
   sum to avg as follows:

          mysql> select a.roll, avg(b.roll) from dice a, dice b 
                 where b.d_id <= a.d_id group by a.d_id, a.roll;           

	  +------+-------------+                                                                                          
	  | roll | avg(b.roll) |                                                                                          
	  +------+-------------+                                                                                          
	  |    1 |      1.0000 |                                                                                          
	  |    2 |      1.5000 |                                                                                          
	  |    3 |      2.0000 |                                                                                          
	  |    4 |      2.5000 |                                                                                          
	  |    5 |      3.0000 |                                                                                          
	  |    6 |      3.5000 |                                                                                          
	  +------+-------------+                                                                                          
	  6 rows in set (0.00 sec)                                                                                        
	                                                                                                                  

                                                                        
TIP 24:

   What commands were executed?  Reading the log files. (By default on 4.1.2 is should
   be setup, but, see TIP 25 to check.)

   The following is an example of the "show binlog events" which will show 
   all the inserted and deleted commands.  See "TIP 2:" which shows how to
   clean up or reset with the "reset master".
   

   mysql> show binlog events;                                                                                                
   show binlog events;                                                                                                       
   +------------------------+-----+------------+-----------+--------------+--------------------------------------------+     
   | Log_name               | Pos | Event_type | Server_id | Orig_log_pos | Info                                       |     
   +------------------------+-----+------------+-----------+--------------+--------------------------------------------+     
   | third-fl-71-bin.000001 |   4 | Start      |         1 |            4 | Server ver: 4.1.1-alpha-log, Binlog ver: 3 |     
   | third-fl-71-bin.000001 |  79 | Query      |         1 |           79 | use `test`; create table j2 (a int)        |     
   | third-fl-71-bin.000001 | 137 | Query      |         1 |          137 | use `test`; insert into j2 (a) values (3)  |     
   | third-fl-71-bin.000001 | 201 | Query      |         1 |          201 | use `test`; insert into j2 (a) values (9)  |     
   | third-fl-71-bin.000001 | 265 | Query      |         1 |          265 | use `test`; delete from j2 where a=3       |     
   +------------------------+-----+------------+-----------+--------------+--------------------------------------------+     
   5 rows in set (0.00 sec)                                                                                                  
                                                                                                                             
   Note that the logfiles by default are located in "/usr/local/var" (see TIP 25:) or execute "mysql> show variables" and look
   for the entry under the datadir. It's possible to query specific logfiles if they exist:

     mysql> show binlog events in 'third-fl-71-bin.000001';

   It's also possible to specify the start position and limit the number of records as follows:

    mysql> show binlog events from 201 limit 2;                                                                          
    show binlog events from 201 limit 2;                                                                                 
    +------------------------+-----+------------+-----------+--------------+-------------------------------------------+ 
    | Log_name               | Pos | Event_type | Server_id | Orig_log_pos | Info                                      | 
    +------------------------+-----+------------+-----------+--------------+-------------------------------------------+ 
    | third-fl-71-bin.000001 | 201 | Query      |         1 |          201 | use `test`; insert into j2 (a) values (9) | 
    | third-fl-71-bin.000001 | 265 | Query      |         1 |          265 | use `test`; delete from j2 where a=3      | 
    +------------------------+-----+------------+-----------+--------------+-------------------------------------------+ 
    2 rows in set (0.00 sec)                                                                                             

  
   Or, from the command line:

       $ mysqlbinlog  <logfile> 

   Another option is writing a C api.  For a complete example of this (selectbinlog.c) download the following:

     http://prdownloads.sourceforge.net/souptonuts/mysql_select-0.0.1.tar.gz?download

       or take a peek at

     http://souptonuts.sourceforge.net/code/selectbinlog.c.html

   This sample "selectbinlog.c" selects only the "Query" events.  The code is pretty simple.

   FYI: If you do a lot of tracking, you may want to write the information to a Berkeley DB. 
   Contrary to the name Berkeley DB is not a database but a hash, or there is an option for
   b-tree format.  MySQL can use Berkeley DB for the underlying table structure. It's very fast,
   and you won't get logs of your logs.  If you're using Linux, Berkeley DB is already installed
   on your system.  Ok, so how does one use Berkeley DB? Samples can be found at the following
   link.  Look for berkeleydb_0.x.x.tar.gz at the following link

     http://sourceforge.net/project/showfiles.php?group_id=79066&package_id=80379

   The newer version of Berkeley DB also supports encryption.  Berkeley DB is open source
   and can be downloaded from the following:

     http://www.sleepycat.com/

   NOTE: MySQL uses Berkeley DB if configured for it

            ./configure --with-berkeley-db 

         And tables are created with the "ENGINE" or "TYPE" 
         option:

            CREATE TABLE b (i INT) ENGINE = BDB;
                        or
            CREATE TABLE b (i INT) TYPE = BDB;
       


TIP 25:

   Setting the binary log file.  This can be configured in /etc/my.cnf

        # The MySQL server                                 
        [mysqld]                                           
        port            = 3306                             
        socket          = /tmp/mysql.sock                  
        log-bin         = /usr/local/var/mysqlLOGb.log     
        log             = /usr/local/var/mysqlLOG.log      

   or for the default hostname just log-bin

        [mysqld]                                          
   	port            = 3306                            
	socket          = /tmp/mysql.sock                 
	log-bin            

   IMPORTANT: The default my.cnf has a "log-bin" entry.  Make sure it is not listed twice.

   It's also possible to set the text log file.  Note both "log-bin" and "log" are set here:

        [mysqld]                                            
	port            = 3306                              
	socket          = /tmp/mysql.sock                   
	log-bin         = /usr/local/var/mysqlLOGb.log  
	log             = /usr/local/var/mysqlLOG.log   

   Note, (see TIP 24:) the binary log file works with the "show binlog events command" and shows, updates, 
   creates, deletes, and drops, but no select statements. In contrast, the  text log-file "/usr/local/var/mysqlLOG.log" 
   shows the user, and the select statement, all of the above, but it's in a text file.  I believe the text
   format is going away with version 5.0.

   The following command "DOES NOT" give information about these log files:

      mysql> show logs;

   This is only for Berkeley DB type databases before commit on this type of table.  It's not what you want!



TIP 26:

   Free articles and tutorials on normalization.

   a)  http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

   b)  http://www.phpbuilder.com/columns/barry20000731.php3?page=1

   c)  http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q100/1/39.asp&NoWebContent=1&NoWebContent=1

   d)  http://www.gslis.utexas.edu/~l384k11w/normstep.html

   f)  http://www.sims.berkeley.edu/academics/courses/is202/f97/Lecture8/
  
   g)  http://www.microsoft-accesssolutions.co.uk/higher_norm_form.htm

   h)  http://ocw.mit.edu/OcwWeb/Civil-and-Environmental-Engineering/1-264JDatabase--Internet--and-Systems-Integration-TechnologiesFall2002/LectureNotes/index.htm



TIP 27:

   Adding and Compiling a User-Defined function.  

   Here are some examples that can be compiled and used with MySQL 4.1. See below for how to install.
   First, just a taste of what they look like:

            mysql> select lookup("www.yahoo.com");           
	    select lookup("www.yahoo.com");               
	    +-------------------------+                   
   	    | lookup("www.yahoo.com") |                   
	    +-------------------------+                   
	    | 216.109.118.71          |                   
	    +-------------------------+                   
	    1 row in set (0.02 sec)                       

      and

            mysql> select reverse_lookup("216.109.118
	    select reverse_lookup("216.109.118.79");                                               
	    +----------------------------------+     
   	    | reverse_lookup("216.109.118.79") |     
	    +----------------------------------+     
	    | p16.www.dcn.yahoo.com            |     
	    +----------------------------------+     
	    1 row in set (0.02 sec)                  

        Also

            mysql> select sequence(3);                               
	    select sequence(3);                                      
	    +-------------+                                          
	    | sequence(3) |                                          
	    +-------------+                                          
	    |           4 |                                          
	    +-------------+                                          
	    1 row in set (0.01 sec)                                  
	                                                             
	    mysql> select sequence(sequence(3));                     
	    select sequence(sequence(3));                            
	    +-----------------------+                                
	    | sequence(sequence(3)) |                                
	    +-----------------------+                                
	    |                     5 |                                
	    +-----------------------+                                
	    1 row in set (0.00 sec)                                  
	                                                             
	    mysql> select sequence(3.4);                             
	    select sequence(3.4);                                    
	    +---------------+                                        
	    | sequence(3.4) |                                        
	    +---------------+                                        
	    |             4 |                                        
	    +---------------+                                        
	    1 row in set (0.00 sec)                                  


            mysql> create table junk (a varchar(1));
            mysql> insert into junk (a) values ('a'),('b'),('c'),('d'),('e');

	   mysql> select sequence(),a from junk;        
	   select sequence(),a from junk;               
	   +------------+------+                        
	   | sequence() | a    |                        
	   +------------+------+                        
	   |          1 | a    |                        
	   |          2 | b    |                        
	   |          3 | c    |                        
	   |          4 | d    |                        
	   |          5 | e    |                        
	   +------------+------+                        
	   5 rows in set (0.00 sec)                     

      An example of an aggregate function:


            mysql> create table cost (inum int, cost double);                    
	    create table cost (inum int, cost double);                           
	    Query OK, 0 rows affected (0.00 sec)                                 
	                                                                         
	    mysql> insert into cost (inum,cost) values (3,4.5);                  
	    insert into cost (inum,cost) values (3,4.5);                         
	    Query OK, 1 row affected (0.00 sec)                                  
	                                                                         
	    mysql> insert into cost (inum,cost) values (10,21.3);                
	    insert into cost (inum,cost) values (10,21.3);                       
	    Query OK, 1 row affected (0.00 sec)                                  
	                                                                         
	    mysql> select avgcost(inum,cost) from cost;                          
	    select avgcost(inum,cost) from cost;                                 
	    +--------------------+                                               
	    | avgcost(inum,cost) |                                               
	    +--------------------+                                               
	    |            17.4231 |                                               
	    +--------------------+                                               
	    1 row in set (0.00 sec)                                              

   OK, HOW TO COMPILE:  

   For best results mysql-4.1.2-alpha or above configured with the following option:

        --with-mysqld-ldflags=-rdynamic

   The full line of my configure is as follows since I use openssl as well.

   STEP A:

        ./configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags=-rdynamic

        make 
        make install
        /etc/init.d/mysql restart

        (Note: if "/etc/init.d/mysql" does not exist see (TIP 20:) )

   STEP B:

        Under "mysql-4.1.2-alpha/sql"  issue the following command.

          # make udf_example.so

   STEP C:

        As root copy the file to a directory where mysql can find it.

          # cp udf_example.so /usr/lib/udf_example.so
      
   STEP D:

        Load mysql.  You may want to look at udf_example.cc, since it as instructions
        as well.  From here issue the following commands:

            mysql>  CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so";          
	    mysql>  CREATE FUNCTION myfunc_double RETURNS REAL SONAME "udf_example.so";       
	    mysql>  CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "udf_example.so";       
	    mysql>  CREATE FUNCTION sequence RETURNS INTEGER SONAME "udf_example.so";         
	    mysql>  CREATE FUNCTION lookup RETURNS STRING SONAME "udf_example.so";            
	    mysql>  CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "udf_example.so";    
	    mysql>  CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "udf_example.so";   
      
        And the functions can be deleted by the following:

            mysql>  DROP FUNCTION metaphon;
            mysql>  DROP FUNCTION myfunc_double;
            mysql>  DROP FUNCTION myfunc_int;
            mysql>  DROP FUNCTION lookup;
            mysql>  DROP FUNCTION reverse_lookup;
            mysql>  DROP FUNCTION avgcost;


       That's it.  Just run the functions above.  These can serve as templates for writing 
       your own.


TIP 28:
   
   Loading Data into Tables from Text Files.

   Assume you have the following table.


            CREATE TABLE loadtest (          
                pkey int(11) NOT NULL auto_increment,        
                name varchar(20),
                exam int,
                score int,
                timeEnter timestamp(14),                     
                PRIMARY KEY  (pkey)                           
               );

    And you have the following formatted text file as shown
    below with the unix "tail" command:

          $ tail /tmp/out.txt 
	  'name22999990',2,94
	  'name22999991',3,93
	  'name22999992',0,91
	  'name22999993',1,93
	  'name22999994',2,90
	  'name22999995',3,93
	  'name22999996',0,93
	  'name22999997',1,89
	  'name22999998',2,85
	  'name22999999',3,88

    NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not
    present in the "/tmp/out.txt" file. Therefore, to successfully load
    the specific fields issue the following:

         mysql> load data infile '/tmp/out.txt' into table loadtest 
                  fields terminated by ',' (name,exam,score);
    
    

TIP 29:
   
   Referential Integrity with InnoDB tables.

   STEP 1 (First create the tables as InnoDB)


       CREATE TABLE agents (
             akey INTEGER NOT NULL auto_increment PRIMARY KEY,
             ln   varchar(30),
             fn   varchar(25),
             phone varchar(20),
             timeEnter timestamp(14))
             ENGINE = InnoDB;                     

       CREATE TABLE clients (
             ckey INTEGER NOT NULL auto_increment PRIMARY KEY,
             f_akey INTEGER NOT NULL,
             ln   varchar(30),
             fn   varchar(25),
             phone varchar(20),
             FOREIGN KEY (f_akey) REFERENCES agents(akey))
             ENGINE = InnoDB;
             

   STEP 2 (Insert entries -- successful way). 

          mysql> insert into agents (ln,fn,phone) values  ('Anderson','Bob','215-782-2134');

        mysql> select @ckey:=last_insert_id();

        mysql> insert into clients  (f_akey,ln,fn,phone) 
                        values  (@ckey,'Chirico','Abby','215-782-2353');

        myslq> insert into clients  (f_akey,ln,fn,phone) 
                        values  (@ckey,'Payne','Zoe','215-782-2352');

        The "last_insert_id()" must be assigned to a variable, because the client entries
        for the two client keys have the same agent.  After the first insert into the client
        table "last_insert_id()" is incremented, reflecting the new add to the client table.


   STEP 3 (Try to insert a client record without a matching agent  -- unsuccessful way)


          mysql> insert into agents (ln,fn,phone) values  ('Splat','Sporkey','215-782-9987');

              Above is ok

        myslq> insert into clients  (f_akey,ln,fn,phone) 
                        values  (last_insert_id(),'Landis','Susan','215-782-5222');

              Above Ok for the first record, but, below last_insert_id() has been
              incremented and the insert will be incorrect. And probably fail, 
              if there is no matching "akey"  in agents.

        myslq> insert into clients  (f_akey,ln,fn,phone) 
                        values  (last_insert_id(),'Landis','Brian','215-782-5222');

   SPECIAL NOTE (The "clients" table must be dropped before the "agents" table)


TIP 30:

    Is it Possible to do Incremental Backups? And is it possible to restore
    changes for only one user? See TIP 24 and TIP 25 for setting up and 
    using "binlog" and the text log. NOTE: "mysql> show logs;" IS NOT a status
    on these log files -- it's only for Berkeley DB.


    From the shell prompt "mysqlbinlog" will list all the changes. Note your
    binary log file may be in a different directory, and have a different name
    depending on the setting in /etc/my.cnf

      $ mysqlbinlog /usr/local/var/mysqlLOGbin.000001

      #040616  8:41:23 server id 1  log_pos 17465     Query   thread_id=384        
      SET TIMESTAMP=1087389683;                                                    
      CREATE TABLE exams (                                                         
                pkey int(11) NOT NULL auto_increment,                              
                name varchar(15) default NULL,                                     
                exam int(11) default NULL,                                         
                score int(11) default NULL,                                        
                PRIMARY KEY  (pkey)                                                
              ) ENGINE=MyISAM DEFAULT CHARSET=latin1;                              
      # at 17761                                                                   
      #040616  8:41:50 server id 1  log_pos 17761     Intvar                       
      SET INSERT_ID=1;                                                             
      # at 17789                                                                   
      #040616  8:41:50 server id 1  log_pos 17789     Query   thread_id=384        
      SET TIMESTAMP=1087389710;                                                    
      insert into exams (name,exam,score) values ('Bob',1,75);                     
      # at 17879                                                                   
      #040616  8:41:50 server id 1  log_pos 17879     Intvar                       
      ....[SNIPPED]
      # at 18615
      #040616  8:41:50 server id 1  log_pos 18615 	Query	thread_id=384	
      SET TIMESTAMP=1087389710;
      insert into exams (name,exam,score) values ('Sue',4,99);
      # at 18705
      #040616  8:52:35 server id 1  log_pos 18705 	Intvar
      SET INSERT_ID=23000010;
      # at 18733
      # LOAD DATA INFILE '/tmp/out.txt' INTO TABLE `loadtest` FIELDS TERMINATED BY ','..[SNIPPED]
      # file_id: 1  block_len: 200

    Note the thread_id "384", session "040616", TIMESTAMP vales, and actual sql
    statements. With grep, awk, or sed it's possible to weed out unwanted queries.
    This assumes you have a base, or a "full restore" starting point.  

    Compare above to the actual commands listed here:

     CREATE TABLE exams (
          pkey int(11) NOT NULL auto_increment,
          name varchar(15) default NULL,
          exam int(11) default NULL,
          score int(11) default NULL,
          PRIMARY KEY  (pkey)
        ) ENGINE=MyISAM;

     insert into exams (name,exam,score) values ('Bob',1,75);
     insert into exams (name,exam,score) values ('Bob',2,77);
     insert into exams (name,exam,score) values ('Bob',3,78);
     insert into exams (name,exam,score) values ('Bob',4,80);

     insert into exams (name,exam,score) values ('Sue',1,90);
     insert into exams (name,exam,score) values ('Sue',2,97);
     insert into exams (name,exam,score) values ('Sue',3,98);
     insert into exams (name,exam,score) values ('Sue',4,99);

         mysql> load data infile '/tmp/out.txt' into table exams 
                  fields terminated by ',' (name,exam,score);


TIP 31:

     Getting XML and HTML output.  

     Assume you have the table "exams" in the database "test". 
     Then, the following will give you XML output if executed
     from the shell prompt with the "-X" option. For html output
     use the "-H" option.

       $ mysql -X -e "select * from exams" test

               <?xml version="1.0"?>                          
                                                              
	       <resultset statement="select * from exams      
	       ">                                             
	         <row>                                        
	               <pkey>1</pkey>                         
	               <name>Bob</name>                       
	               <exam>1</exam>                         
	               <score>75</score>                      
	         </row>                                       
	                                                      
	         <row>                                        
	               <pkey>2</pkey>                         
	               <name>Bob</name>                       
	               <exam>2</exam>                         
	               <score>77</score>                      
	         </row>                                       
	                                                      
	         <row>                                        
	               <pkey>3</pkey>                         
	               <name>Bob</name>                       
	               <exam>3</exam>                         
	               <score>78</score>                      
	         </row>                                       
	                                                      
	         <row>                                        
	               <pkey>4</pkey>                         
	               <name>Bob</name>                       
	               <exam>4</exam>                         
	               <score>80</score>                      
	         </row>                                       
	                                                      
	         <row>                                        
	               <pkey>5</pkey>                         
	               <name>Sue</name>                       
	               <exam>1</exam>                         
	               <score>90</score>                      
	         </row>                                       
	                                                      
	       </resultset>                    


     Or, (-N is depreciated so use --skip-column-names ) for no heading:

       $ mysql --skip-column-names -e "select * from exams" test

               +---+------+------+------+      
	       | 1 |  Bob |    1 |   75 |
	       | 2 |  Bob |    2 |   77 |
	       | 3 |  Bob |    3 |   78 |
	       | 4 |  Bob |    4 |   80 |
	       | 5 |  Sue |    1 |   90 |
	       | 6 |  Sue |    2 |   97 |
	       | 7 |  Sue |    3 |   98 |
	       | 8 |  Sue |    4 |   99 |
	       +---+------+------+------+

     And, add the "-s" silent option to get the following:


       $ mysql --skip-column-names -s -e "select * from exams" test

              1       Bob     1       75
	      2       Bob     2       77
	      3       Bob     3       78
	      4       Bob     4       80
	      5       Sue     1       90
	      6       Sue     2       97
	      7       Sue     3       98
	      8       Sue     4       99


     To prevent being prompted for a password. Create "/home/chirico/.my.cnf"
     with password and user.  An example is shown below. Unless a user
     is entered "piggy" it will default to "chirico", this user.

           [client]                       
	   user=piggy
	   password=p1ggyp1ssw6r5

     Use this method. It will not show up when doing "ps -aux"


TIP 32:

     Views?  Can you do a "create view" in MySQL? No. Not until version 5.


TIP 33:

     Spatial Extensions - MySQL 4.1 supports spatial extensions.

     Getting the X value of a point.
                                                           
       mysql> SELECT X(GeomFromText('Point(5.02 7.9)'));   

       +------------------------------------+              
       | X(GeomFromText('Point(5.02 7.9)')) |              
       +------------------------------------+              
       |                               5.02 |              
       +------------------------------------+              
       1 row in set (0.00 sec)                             

     Note that a comma separates points, and the x,y values are separated by spaces.

     Getting the length of a line.

                     (1 1)
                     /
                   /
                 /
               (0 0)
                                                                   
         mysql> SELECT GLength(GeomFromText('LineString(0 0,1 1)')); 

         +----------------------------------------------+            
         | GLength(GeomFromText('LineString(0 0,1 1)')) |            
         +----------------------------------------------+            
         |                              1.4142135623731 |            
         +----------------------------------------------+             

     Getting the total length of all the lines making a step.

                  (2 1) ---- (2 2) 
                       |
                       |
             (1 0) ---- (1 1)
                  |
                  |
             (0 0)

                                                                                   
	  mysql> SELECT GLength(GeomFromText('LineString(0 0,1 0,1 1,1 2,2 2)'));  

	  +----------------------------------------------------------+             
	  | GLength(GeomFromText('LineString(0 0,1 0,1 1,1 2,2 2)')) |             
	  +----------------------------------------------------------+             
	  |                                                        4 |             
	  +----------------------------------------------------------+             

      Or the perimiter of the following triangle:

                    (1 1)
                     / \
                   /     \
           (0 0) /________ \ (2 0)

                                                                               
          mysql> SELECT GLength(GeomFromText('LineString(0 0,1 1,2 0,0 0)'));   

	  +------------------------------------------------------+             
	  | GLength(GeomFromText('LineString(0 0,1 1,2 0,0 0)')) |             
	  +------------------------------------------------------+             
	  |                                      4.8284271247462 |             
	  +------------------------------------------------------+             

     Note the following select statement is a 5x5 square with 
     left bottom point at 0 0.


            (5 0)            (5 5)
                  -----------
                 |           |
                 |	     |
                 |	     |
                 |	     |
                  -----------
            (0 0)            (0 5)

	mysql> SELECT Area(GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))'));     
	
	+------------------------------------------------------+                
	| Area(GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))')) |                
	+------------------------------------------------------+                
	|                                                   25 |                
	+------------------------------------------------------+                
	1 row in set (0.00 sec)                                                 

     The area of the inner square "9" minus the area of the outer square "25" is "16".


          5 0  ------------------  5 5
              |  4 1        4 4  |
              |    ----------    |
              |   |          |   |
              |   |          |   |
              |   |          |   |
              |   |          |   |
              |    ----------    |
              |  1 1        1 4  |
          0 0  ------------------  0 5


 	  mysql> SELECT Area(GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0),(1 1,1 4,4 4,4 1,1 1))'));   

	  +----------------------------------------------------------------------------+              
	  | Area(GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0),(1 1,1 4,4 4,4 1,1 1))')) |              
	  +----------------------------------------------------------------------------+              
	  |                                                                         16 |              
	  +----------------------------------------------------------------------------+              
	  1 row in set (0.00 sec)                                                                     

      Reference:
        http://dev.mysql.com/doc/mysql/en/Spatial_extensions_in_MySQL.html
        http://mysql.planetmirror.com/doc/mysql/en/Populating_spatial_columns.html
        http://www.ctch.net/mysql/manual_Spatial_extensions_in_MySQL.html



TIP 34:

     Creating Tables for Spatial Extensions.

           mysql> CREATE TABLE spat (p POINT, g POLYGON, l LINESTRING, geo GEOMETRY );

     Note, GEOMETRY is general and will take the following values:

                    - POINT
                    - POLYGON 
                    - LINESTRING
                    - MULTIPOINT 
                    - MULTILINESTRING
                    - MULTIPOLYGON 
                    - GEOMETRYCOLLECTION 
 
     Inserting values.

           mysql>  INSERT INTO spat (p) VALUES (GeomFromText('POINT(1 2)'));

     Displaying values:

	   mysql> select X(p),Y(p) from spat;                                                                                             

	   +------+------+                     
	   | X(p) | Y(p) |                     
	   +------+------+                     
	   |    1 |    2 |                     
	   +------+------+                  

     Note with "geo" it's possible to insert combinations

          mysql> insert into spat(geo) values 
                     (GeomFromText('MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))'));   

     Now, to see what's in "geo", do the following:

          mysql> select AsText(geo) from spat;

                +-------------------------------------------------------------------+             
		| AsText(geo)                                                       |
		+-------------------------------------------------------------------+
		| POINT(1 2)                                                        |
		| MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7,5 5))) |
		+-------------------------------------------------------------------+
		2 rows in set (0.02 sec)                                             



TIP 35:

     Working with Spatial Relationship Functions.

        ... example will follow



TIP 36:

     Difficult Column Names, Like `DATE` -- use backtick.

     If using "date" as a column name, enclose it in backticks ` as follows:

          CREATE TABLE IF NOT EXISTS stocks (
              pkey int(11) NOT NULL auto_increment,
              `date` date,
              ticker varchar(5),
              open float,
              high float,
              low  float,
              close float,
              volume  int,
              timeEnter timestamp(14),
             PRIMARY KEY  (pkey)

             ) ;

       To get 1 day old data reference:
           http://biz.swcp.com/stocks/#Daily update

     Statement to load data:

          mysql>  load data infile '/tmp/sp500hst.txt' into table stocks 
                    fields terminated by ',' (date,ticker,open,high,low,close,volume);







LONGWINDED TIPS: 


LONGWINDED TIP 1: (May want to skip, since it's a long example)

     SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT". 
     Yes, there is use for this..."if" statements sometimes cause problems
     when used in combination.

     The simple secret, and it's also why they work in almost all databases, is the 
     following functions:

       o   sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively
       o   abs( sign( x) )  returns 0 if x = 0  else, 1 if  x > 0 or x < 0
       o   1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0
 
        Quick example:   sign(-1) = -1,  abs( sign(-1) ) = 1,  1-abs( sign(-1) ) = 0

     Data for full example:

           CREATE TABLE exams (
             pkey int(11) NOT NULL auto_increment,
             name varchar(15),
             exam int,
             score int,
             PRIMARY KEY  (pkey)
           );

           insert into exams (name,exam,score) values ('Bob',1,75);      
           insert into exams (name,exam,score) values ('Bob',2,77);      
           insert into exams (name,exam,score) values ('Bob',3,78);      
           insert into exams (name,exam,score) values ('Bob',4,80);      
                                                                         
           insert into exams (name,exam,score) values ('Sue',1,90);      
           insert into exams (name,exam,score) values ('Sue',2,97);      
           insert into exams (name,exam,score) values ('Sue',3,98);      
           insert into exams (name,exam,score) values ('Sue',4,99);      
                                                                                                                 
     mysql> select * from exams;                                                                                 
     +------+------+------+-------+                                                                              
     | pkey | name | exam | score |                                                                              
     +------+------+------+-------+                                                                              
     |    1 | Bob  |    1 |    75 |                                                                              
     |    2 | Bob  |    2 |    77 |                                                                              
     |    3 | Bob  |    3 |    78 |                                                                              
     |    4 | Bob  |    4 |    80 |                                                                              
     |    5 | Sue  |    1 |    90 |                                                                              
     |    6 | Sue  |    2 |    97 |                                                                              
     |    7 | Sue  |    3 |    98 |                                                                              
     |    8 | Sue  |    4 |    99 |                                                                              
     +------+------+------+-------+                                                                              
     8 rows in set (0.00 sec)                                                                                    
                                                                                                                 
     mysql> select name,                                                                                         
     sum(score*(1-abs(sign(exam-1)))) as exam1,                                                                  
     sum(score*(1-abs(sign(exam-2)))) as exam2,                                                                  
     sum(score*(1-abs(sign(exam-3)))) as exam3,                                                                  
     sum(score*(1-abs(sign(exam-4)))) as exam4                                                                   
     from exams group by name;                                                                                   
                                                                                                                 
     +------+-------+-------+-------+-------+                                                                    
     | name | exam1 | exam2 | exam3 | exam4 |                                                                    
     +------+-------+-------+-------+-------+                                                                    
     | Bob  |    75 |    77 |    78 |    80 |                                                                    
     | Sue  |    90 |    97 |    98 |    99 |                                                                    
     +------+-------+-------+-------+-------+                                                                    
     2 rows in set (0.00 sec)                                                                                    
                                                                                                                 
     Note, the above pivot table was created with one select statement                                           

     You may think IF's would be cleaner. NO, WATCH OUT!
     Look the following gives INCORRECT RESULTS!

     mysql> select name,                                                                                         
     if(exam=1,score,null) as exam1,                                                                  
     if(exam=2,score,null) as exam2,                                                                  
     if(exam=3,score,null) as exam3,                                                                  
     if(exam=4,score,null) as exam4                                                                  
     from exams group by name;                                                                                   

     +------+-------+-------+-------+-------+  
     | name | exam1 | exam2 | exam3 | exam4 |    
     +------+-------+-------+-------+-------+  
     | Bob  |    75 |  NULL |  NULL |  NULL |  
     | Sue  |    90 |  NULL |  NULL |  NULL |  
     +------+-------+-------+-------+-------+  
     2 rows in set (0.00 sec)                                                                                                  

     Note the above gives indeterminate results.

     Paul DuBois [ paul at snake.net ] showed me the correct way to 
     perform this select statement. According to him

        When you include a GROUP BY clause in a query, the only values you       
	can select are the grouped columns or summary values calculated          
	from the groups.  If you display additional columns, they're not         
	tied to the grouped columns and the values displayed for them are        
	indeterminate.                                                           
	                                                                         
	If you rewrite the query like this, you get the correct result:          
	                                                                         
	select name,                                                             
	     sum(if(exam=1,score,null)) as exam1,                                
	     sum(if(exam=2,score,null)) as exam2,                                
	     sum(if(exam=3,score,null)) as exam3,                                
	     sum(if(exam=4,score,null)) as exam4                                 
	     from exams group by name;                                           
	                                                                         
	+------+-------+-------+-------+-------+                                 
	| name | exam1 | exam2 | exam3 | exam4 |                                 
	+------+-------+-------+-------+-------+                                 
	| Bob  |    75 |    77 |    78 |    80 |                                 
	| Sue  |    90 |    97 |    98 |    99 |                                 
	+------+-------+-------+-------+-------+                                 

     
     mysql> select name,                                                                                         
            sum(score*(1-abs(sign(exam-1)))) as exam1,                                                           
            sum(score*(1-abs(sign(exam-2)))) as exam2,                                                           
            sum(score*(1-abs(sign(exam-3)))) as exam3,                                                           
            sum(score*(1-abs(sign(exam-4)))) as exam4,                                                           
              sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,              
              sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,              
              sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4               
            from exams group by name;                                                                            
     +------+-------+-------+-------+-------+-----------+-----------+-----------+                                
     | name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |                                
     +------+-------+-------+-------+-------+-----------+-----------+-----------+                                
     | Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |                                
     | Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |                                
     +------+-------+-------+-------+-------+-----------+-----------+-----------+                                
     2 rows in set (0.00 sec)                                                                                    
                                                                                                                 
     Above delta_1_2 shows the difference between the first and second exams, with the numbers                   
     being positive because both Bob and Sue improved their score with each exam.  Calculating                   
     the deltas here shows it's possible to compare two rows, not columns which is easily done                   
     with the standard  SQL statements but  rows in the original table.                                          
                                                                                                                 
     mysql>select name,                                                                                          
     sum(score*(1-abs(sign(exam-1)))) as exam1,                                                                  
     sum(score*(1-abs(sign(exam-2)))) as exam2,                                                                  
     sum(score*(1-abs(sign(exam-3)))) as exam3,                                                                  
     sum(score*(1-abs(sign(exam-4)))) as exam4,                                                                  
       sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,                     
       sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,                     
       sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,                     
                                                                                                                 
       sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +                                
       sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +                                
       sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints                
       from exams group by name;                                                                                 
                                                                                                                 
     +------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+               
     | name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |               
     +------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+               
     | Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 |               
     | Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 |               
     +------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+               
     2 rows in set (0.00 sec)                                                                                    
                                                                                                                 
     TotalIncPoints shows the sum of the deltas.                                                                 
                                                                                                                 
     select name,                                                                                                
     sum(score*(1-abs(sign(exam-1)))) as exam1,                                                                  
     sum(score*(1-abs(sign(exam-2)))) as exam2,                                                                  
     sum(score*(1-abs(sign(exam-3)))) as exam3,                                                                  
     sum(score*(1-abs(sign(exam-4)))) as exam4,                                                                  
       sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,                     
       sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,                     
       sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,                     
                                                                                                                 
                                                                                                                 
       sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +                                
       sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +                                
       sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints,               
                                                                                                                 
     (sum(score*(1-abs(sign(exam-1)))) +                                                                         
     sum(score*(1-abs(sign(exam-2)))) +                                                                          
     sum(score*(1-abs(sign(exam-3)))) +                                                                          
     sum(score*(1-abs(sign(exam-4)))))/4 as AVG                                                                  
                                                                                                                 
     from exams group by name;                                                                                   
                                                                                                                 
     +------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+       
     | name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG   |       
     +------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+       
     | Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 | 77.50 |       
     | Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 | 96.00 |       
     +------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+       
     2 rows in set (0.00 sec)                                                                                    
                                                                                                                 
     It's possible to combine Total Increasing Point TotalIncPoints with AVG.  In fact, it's possible to combine 
     all of the example cuts of the data into one SQL statement, which provides additional options for displaying
     data on your page                                                                                           
                                                                                                                 
     select name,                                                                                                
     sum(score*(1-abs(sign(exam-1)))) as exam1,                                                                  
     sum(score*(1-abs(sign(exam-2)))) as exam2,                                                                  
     sum(score*(1-abs(sign(exam-3)))) as exam3,                                                                  
     sum(score*(1-abs(sign(exam-4)))) as exam4,                                                                  
                                                                                                                 
     (sum(score*(1-abs(sign(exam-1)))) +                                                                         
     sum(score*(1-abs(sign(exam-2)))))/2  as AVG1_2,                                                             
                                                                                                                 
     (sum(score*(1-abs(sign(exam-2)))) +                                                                         
     sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,                                                              
                                                                                                                 
     (sum(score*(1-abs(sign(exam-3)))) +                                                                         
     sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,                                                              
                                                                                                                 
     (sum(score*(1-abs(sign(exam-1)))) +                                                                         
     sum(score*(1-abs(sign(exam-2)))) +                                                                          
     sum(score*(1-abs(sign(exam-3)))) +                                                                          
     sum(score*(1-abs(sign(exam-4)))))/4 as AVG                                                                  

     from exams group by name;                                                                                   
                                                                                                                 
     +------+-------+-------+-------+-------+--------+--------+--------+-------+                                 
     | name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG   |                                 
     +------+-------+-------+-------+-------+--------+--------+--------+-------+                                 
     | Bob  |    75 |    77 |    78 |    80 |  76.00 |  77.50 |  79.00 | 77.50 |                                 
     | Sue  |    90 |    97 |    98 |    99 |  93.50 |  97.50 |  98.50 | 96.00 |                                 
     +------+-------+-------+-------+-------+--------+--------+--------+-------+                                 
     2 rows in set (0.00 sec)                                                                                    
                                                                                                                 
     Exam scores are listing along with moving averages...again it's all with one                                
     select statement.                                                                                           



REFERENCES:

Good article on Spatial Extensions with MySQL
http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

Good article on "Cross tabulations" or de-normalizing data to show stats:
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html

MySQL performance tuning tips:
http://www.linux-mag.com/cgi-bin/printer.pl?issue=2001-06&article=mysql

See (TIP 26:) for links on normalization.

Berkeley DB sample programs:
 Look for berkeleydb_0.0.24.tar.gz or the latest version at:
 http://sourceforge.net/project/showfiles.php?group_id=79066&package_id=80379

RECOMMENDED READING:

    "MySQL Cookbook", Paul DuBois, O'Reilly, ISBN: 0-596-00145-2

    "High Performance MySQL", Derek J. Balling, Jeremy Zawodny, ISBN: 0-596-00306-4

    "Database Design for Mere Mortals", Michael J. Hernandez, Addison-Wesley

    "Securing MySQL: step-by-step",Artur Maj  (See link below)
            http://www.securityfocus.com/infocus/1726
