473,554 Members | 3,168 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MySQL tips that I have come across

Interesting Things to Know about MySQL
Mike Chirico (mc******@users .sourceforge.ne t)
Copyright (GPU Free Documentation License) 2004
Last Updated: Mon Jun 7 10:37:28 EDT 2004

The latest version of this document can be found at:
http://prdownloads.sourceforge.net/s...l.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;

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 "automatica lly 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_FORMA T(timeEnter,'%m-%d-%Y %T') from t;

+------+------+---------------------------------------+---------------------
-----------------+
| a | b | DATE_FORMAT(tim eUpdate,'%m-%d-%Y %T') |
DATE_FORMAT(tim eEnter,'%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 mc******@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="your database";
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;data base=${database }";
my $dbh = DBI->connect ($dsn, $user, $passwd)
or die "Cannot connect to server\n";

my $s = $dbh->prepare($tblcr eate);
$s->execute();
$s = $dbh->prepare($inser t);
$s->execute();

$s = $dbh->prepare($selec t);
$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 entires. 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, leave 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,lo g_02) INSERT_METHOD=L AST;
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( ).

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,p type) 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,pkeykeyt est) values
('one',LAST_INS ERT_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".

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.4 131 > 192.168.1.71.my sql: S [tcp sum ok]
2542149285:2542 149285(0) win 16384 <mss 1460,nop,nop,sa ckOK> (DF) (ttl 128,
id 2873, len 48)
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.4 131 > 192.168.1.71.my sql: S [tcp sum ok]
2542149285:2542 149285(0) win 16384 <mss 1460,nop,nop,sa ckOK> (DF) (ttl 128,
id 2878, len 48)

TIP 11:

Create a C or C++ API

Download the following:
http://prdownloads.sourceforge.net/s...ar.gz?download

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

TIP 12:

Connect from Java

#
# mysql-connector-java-3.0.11-stable-bin.jar or later must be
downloaded
# from:http://dev.mysql.com/downloads/connector/j/3.0.html
#
# Compile:
# javac Connect.java
#
# Run:
# java Connect

import java.sql.*;

public class Connect
{
public static void main (String[ ] args)
{
Connection conn = null;
String url = "jdbc:mysql ://localhost/";
String userName = "root";
String password = "";

try
{
Class.forName
("com.mysql.jdb c.Driver").newI nstance ( );
conn = DriverManager.g etConnection (url,
userName, password);
System.out.prin tln ("Connected" );
}
catch (Exception e)

{
System.err.prin tln ("Cannot connect to
server");
}
finally
{
if (conn != null)
{
try
{
conn.close ( );
System.out.prin tln ("Disconnected" );
}
catch (Exception e) { /* ignore close errors
*/ }
}
}

}
}

TIP 13:

Print defaults for the current client connection
$ /usr/local/bin/my_print_defaul ts 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_encr ypt('secret','s omekey'));
insert into password (id,password)
values ('tom',des_encr ypt('password', 'somekey'));
insert into password (id,password)
values ('kate',des_enc rypt('desmark', 'somekey'));
insert into password (id,password)
values ('tim',des_encr ypt('tim','some key'));
insert into password (id,password)
values ('sue',des_encr ypt('SUM','some key'));
insert into password (id,password)
values ('john',des_enc rypt('dotgo86', 'somekey'));
insert into password (id)
values ('randal');


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

+--------+---------------------------------+
| id | des_decrypt(pas sword,'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','pass word', 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(pas sword,'somekey' ))
,concat('passwo rd,secret,simpl e,',lcase(id),' ,test'))))
),

substring('n',1 ,
sign(abs(
find_in_set( lcase( des_decrypt(pas sword,'somekey' ))
,concat('passwo rd,secret,simpl e,',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(pas sword,'somekey' ))
,concat('passwo rd,secret,simpl e,',lcase(id),' ,test'))))
),

substring('n',1 ,
sign(abs(
find_in_set( lcase( des_decrypt(pas sword,'somekey' ))
,concat('passwo rd,secret,simpl e,',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:

Minimum configure tip:

./configure --with-openssl --enable-thread-safe-client

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.


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_in sert_id();

insert into child(pkey,cnam e) values (@a_lid,'a1');
insert into child(pkey,cnam e) values (@a_lid,'a2');
insert into child(pkey,cnam e) values (@a_lid,'a3');
insert into parent(pname) values ('B');
select @a_lid:=last_in sert_id();
insert into child(pkey,cnam e) values (@a_lid,'b1');
insert into child(pkey,cnam e) values (@a_lid,'b2');
insert into parent(pname) values ('C');

mysql> select p.*,c.cname,cou nt(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)

mysql>

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...indepen dent 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>

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.

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)

Another option is writing a C api. For a complete example of this
(selectbinlog.c ) download the following:
http://prdownloads.sourceforge.net/s...ar.gz?download

or take a peek at

http://souptonuts.sourceforge.net/co...tbinlog.c.html

This sample selects only the "Query" events.

TIP 25:

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

[mysqld]
port = 3306
socket = /tmp/mysql.sock
log-bin = /usr/local/var/mysqlLOGb.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

[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" ,however, the
text logfile "/usr/local/var/mysqlLOG.log" shows which user was executing
commands.
TIP 26:

Free articles and tutorials on normalization.

a)
http://dev.mysql.com/tech-resources/...alization.html

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

c)
http://support.microsoft.com/default...NoWebContent=1

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

f) http://www.sims.berkeley.edu/academi.../f97/Lecture8/

g)
http://ocw.mit.edu/OcwWeb/Civil-and-...otes/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.yah oo.com");
select lookup("www.yah oo.com");
+-------------------------+
| lookup("www.yah oo.com") |
+-------------------------+
| 216.109.118.71 |
+-------------------------+
1 row in set (0.02 sec)

and

mysql> select reverse_lookup( "216.109.11 8
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(sequen ce(3));
select sequence(sequen ce(3));
+-----------------------+
| sequence(sequen ce(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)
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,co st) from cost;
select avgcost(inum,co st) from cost;
+--------------------+
| avgcost(inum,co st) |
+--------------------+
| 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

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.











































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

SQL Characteristic Functions: Do it without "if", "case", or
"GROUP_CONC AT". There's little
use for this now; but, it's fast and provides a lot of control.

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,scor e) values ('Bob',1,75);
insert into exams (name,exam,scor e) values ('Bob',2,77);
insert into exams (name,exam,scor e) values ('Bob',3,78);
insert into exams (name,exam,scor e) values ('Bob',4,80);

insert into exams (name,exam,scor e) values ('Sue',1,90);
insert into exams (name,exam,scor e) values ('Sue',2,97);
insert into exams (name,exam,scor e) values ('Sue',3,98);
insert into exams (name,exam,scor e) 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 clean bug WATCH OUT!
Look what the following gives (INCORRECT !!):

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)

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...agai n it's all
with one
select statement.
REFERENCES:

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

MySQL performance tuning tips:
http://www.linux-mag.com/cgi-bin/pri...&article=mysql

See (TIP 26:) for links on normalization.
Jul 20 '05 #1
0 3930

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
9883
by: Matthew Clubb | last post by:
Hi, I need help developing an expanding form I've decided that a use of PHP, Mysql and Javascript is the best platform for creating a selection of database interfaces which I'm trying to build for my company. I had been using Microsoft Access, but obviously this requires licenses for every machine. BUT..... I'm look for an easy way of...
0
1852
by: Ola Ogunneye | last post by:
--=_7B255FAE.1F7E0C30 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Hello all, Let me start out by saying that I am still a newbie. I just freshly installed mysql version 4.0.13. The following are the steps that I took.
0
1359
by: Ralph Guzman | last post by:
Paul, I just picked up your book today: MySQL Cookbook and I just wanted to comment on how great it is. I've spent hours at the bookstore looking through mySQL books, and by far this is the best one I come across. I strongly recommend it to anybody--Beginners, Intermediate, or Advanced Users.
0
6672
by: Donald Tyler | last post by:
Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to be able to place the PHP file on the server, and I guess you probably can't do that either. Talk about catch 22... The only other way I can think...
7
2875
by: Charles Crume | last post by:
Hello all; I have used dBASE, and other computer languages/databases, for years. They all have a logical field type. However, the version of MySQL used by the ISP hosting my site does not support a "logical" field type. It does support ENUM and I have set some up in a couple of tables that accept the values 'T' and 'F'. Sometimes they...
67
12455
by: Bob Powell | last post by:
To whom it may concern: I find the recent articles in various trade publications a little disturbing due to the lack of PostgrSQL mention. I continue to see articles about how IBM may be considering MYSQL for development an open_source web database. Why isn't PostgreSQL being considered or talked about by major industry giants? As a...
15
4576
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to communicate with a MySQL database table on a web server, from inside of my company's Access-VBA application. I know VBA pretty well but have never...
0
2208
by: taras.di | last post by:
Hi everyone, I've come across an extremely strange problem. The exact same query in both mysql command line client, and mysql query browser gives entirely different results. I was hoping someone out there could shed some light. Ok, the query (I've stripped it bare, the real query is a bit more complex)...
3
2982
by: filip.norrgard | last post by:
Hi All! I've been developing an ASP.Net 2.0 web application using the Visual Studio 2005 tools. Currently datagrids on a page are filled with data from a dataset (a .xsd file in the "project") that, in turn, gets the data from MySql server. Locally testing it on my PC (Windows XP SP2, FYI) it works and no errors appear. However, when I...
0
7589
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8029
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7881
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6131
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5428
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5147
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3550
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2012
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1121
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.