PHP FAQ
MYSQL Tricks
PHP MYSQL INTERVIEW
PHP TIPS & Tricks
MySQL tricks and tips
MySQL is an extremely stable, highly popular open source database that ranks high
on speed rating. It is the default choice for most web hosts to provide to their
customers because of its ease of deployment and use.
However, a good database can only be as good
as it's administrator is. Of course not all of us are seasoned database administrators
so this article is meant for novice and not so novice users. It provides some
simple tricks and tips to enhance the performance of your database.
If you would like to use Foreign keys then use
table type = InnoDB which supports foreign keys. This feature is supported in
mySQL version 3.23.44 and upwards.
A simple example is
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;
Seek time is the time required for a disk to
find a piece of data. This is a bottleneck of the disk and can be improved by
distributing data over many disks.
To reset the auto increment value if you have deleted rows and want to add new
ones, use the command : TRUNCATE TABLE (TABLENAME)
When creating a new column in your database table, use the smallest data type
that will hold the information you expect to store. Most of the time, using
INT is overdoing it as MEDIUMINT, SMALLINT, or TINYINT have enough range. Using
a smaller data type saves space and speeds things up.
Declaring columns as NOT NULL, unless you specifically plan to store NULL values
is advised as it will save space and speed things up.
Date format can be done on the fly by using the built in mySQL function DATE_FORMAT.
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)
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 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 my 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().
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".
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 (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.4131 > 192.168.1.71.mysql: S [tcp sum ok] 2542149285:2542149285(0)
win 16384 (DF) (ttl 128, id 2878, len 48)
TIP 11:
Create a C or C++ API
Download the following:
http://prdownloads.sourceforge.net/souptonuts/mysql_select-0.0.1.tar.gz?...
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.jdbc.Driver").newInstance ( );
conn = DriverManager.getConnection (url, userName, password);
System.out.println ("Connected");
}
catch (Exception e)
{
System.err.println ("Cannot connect to server");
}
finally
{
if (conn != null)
{
try
{
conn.close ( );
System.out.println ("Disconnected");
}
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:
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_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)
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...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>
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);
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)
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_CONCAT". 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,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 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...again it's all with one
select statement.
