Old challenges, new synthax ...

I've blogged before about the new SQL synthax which is becoming available in databases and how it helps solving questions which are increasingly common.

Now it's time for another example, something which doesn't come up often in a reporting environment as most tools have this feature, but can be a problem if you're building your output with a scipting language.

Adding a "Total" row at the end of your tabular output, that's it! Here you can find the Devshed forum post that prompted this summary.

Aside from the classical solution using a UNION and an ORDER BY query, I suggested using the new WITH ROLLUP clause, which is implemented by many commercial databases and MySQL (available from 4.1).

Unfortunately MySQL's implementations poses some limitations to the ORDER BY clause, actually it's impossible to directly order by the aggregate column, which is a quite common requirement, on with the main problem using the Sakila sample database.

An example requirement could be showing all sales by category and a row holding the grand total.

In Sakila the basic query would be:

SELECT
 c.name AS category,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
GROUP BY
 c.name; 

As I said a requirement based on ordering by total_sales in MySQL can't be satisfied without using a trick, like nesting selects, and so I'll skip it (BTW: this made me notice a bad practice left around in Sakila, usage of order by in views, see "sales_by_film_category") .

The traditional solution for adding a total row would be:

SELECT
 c.name AS category,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
GROUP BY
 c.name
UNION ALL
SELECT
 NULL,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
ORDER
    BY CASE WHEN category IS NULL
            THEN 'last'
            ELSE 'first' END
     , category 

As you can see it can be quite hard to read ... the order by trick is last.

The explain plan can become quite nasty too ... (MySQLPerformanceBlog wrote about this earlier), now have a look at the more modern solution:

SELECT
 c.name AS category,
 SUM(p.amount) AS total_sales
FROM
 sakila.payment p
  JOIN sakila.rental r
   ON p.rental_id = r.rental_id
  JOIN sakila.inventory i
   ON r.inventory_id = i.inventory_id
  JOIN sakila.film f
   ON i.film_id = f.film_id
  JOIN sakila.film_category fc
   ON f.film_id = fc.film_id
  JOIN sakila.category c
   ON fc.category_id = c.category_id
GROUP BY
 c.name
WITH rollup; 

Much simpler, isn't it?

But what about efficiency?
Let's look at explain plans for those queries:

First one, the classic way:

mysql> EXPLAIN EXTENDED select
->  c.name AS category,
->  sum(p.amount) AS total_sales
-> from
->  sakila.payment p
->   join sakila.rental r
->    on p.rental_id = r.rental_id
->   join sakila.inventory i
->    on r.inventory_id = i.inventory_id
->   join sakila.film f
->    on i.film_id = f.film_id
->   join sakila.film_category fc
->    on f.film_id = fc.film_id
->   join sakila.category c
->    on fc.category_id = c.category_id
-> group by
->  c.name
-> union all
-> select
->  null,
->  sum(p.amount) AS total_sales
-> from
->  sakila.payment p
->   join sakila.rental r
->    on p.rental_id = r.rental_id
->   join sakila.inventory i
->    on r.inventory_id = i.inventory_id
->   join sakila.film f
->    on i.film_id = f.film_id
->   join sakila.film_category fc
->    on f.film_id = fc.film_id
->   join sakila.category c
->    on fc.category_id = c.category_id
-> ORDER
->     BY CASE WHEN category IS NULL
->             THEN 'last'
->             ELSE 'first' END
->      , category;
+----+--------------+------------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+---------------------------------+
| id | select_type  | table      | type   | possible_keys                     |key                       | key_len | ref                   | rows | filtered |Extra                           |
+----+--------------+------------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+---------------------------------+
|  1 | PRIMARY      | c          | ALL    | PRIMARY                           |NULL                      | NULL    | NULL                  |   16 |   100.00 |Using temporary; Using filesort |
|  1 | PRIMARY      | fc         | ref    | PRIMARY,fk_film_category_category |fk_film_category_category | 1       | sakila.c.category_id  |    9 |   100.00 |Using index                     |
|  1 | PRIMARY      | f          | eq_ref | PRIMARY                           |PRIMARY                   | 2       | sakila.fc.film_id     |    1 |   100.00 |Using index                     |
|  1 | PRIMARY      | i          | ref    | PRIMARY,idx_fk_film_id            |idx_fk_film_id            | 2       | sakila.fc.film_id     |    2 |   100.00 |Using index                     |
|  1 | PRIMARY      | r          | ref    | PRIMARY,idx_fk_inventory_id       |idx_fk_inventory_id       | 3       | sakila.i.inventory_id |    1 |   100.00 |Using index                     |
|  1 | PRIMARY      | p          | ref    | fk_payment_rental                 |fk_payment_rental         | 5       | sakila.r.rental_id    |    1 |   100.00 |Using where                     |
|  2 | UNION        | c          | index  | PRIMARY                           |PRIMARY                   | 1       | NULL                  |   16 |   100.00 |Using index                     |
|  2 | UNION        | fc         | ref    | PRIMARY,fk_film_category_category |fk_film_category_category | 1       | sakila.c.category_id  |    9 |   100.00 |Using index                     |
|  2 | UNION        | f          | eq_ref | PRIMARY                           |PRIMARY                   | 2       | sakila.fc.film_id     |    1 |   100.00 |Using index                     |
|  2 | UNION        | i          | ref    | PRIMARY,idx_fk_film_id            |idx_fk_film_id            | 2       | sakila.fc.film_id     |    2 |   100.00 |Using index                     |
|  2 | UNION        | r          | ref    | PRIMARY,idx_fk_inventory_id       |idx_fk_inventory_id       | 3       | sakila.i.inventory_id |    1 |   100.00 |Using index                     |
|  2 | UNION        | p          | ref    | fk_payment_rental                 |fk_payment_rental         | 5       | sakila.r.rental_id    |    1 |   100.00 |Using where                     |
| NULL | UNION RESULT |  | ALL    | NULL| NULL                      | NULL    | NULL                  | NULL |     NULL| Using filesort                  |
+----+--------------+------------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+---------------------------------+
13 rows in set, 1 warning (0.00 sec)

mysql>

Pretty bad isn't it? Two queries are executed, then glued together.

Now the second one WITH ROLLUP:

mysql> EXPLAIN EXTENDED select
->  c.name AS category,
->  sum(p.amount) AS total_sales
-> from
->  sakila.payment p
->   join sakila.rental r
->    on p.rental_id = r.rental_id
->   join sakila.inventory i
->    on r.inventory_id = i.inventory_id
->   join sakila.film f
->    on i.film_id = f.film_id
->   join sakila.film_category fc
->    on f.film_id = fc.film_id
->   join sakila.category c
->    on fc.category_id = c.category_id
-> group by
->  c.name
-> with rollup;
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+----------------+
| id | select_type | table | type   | possible_keys                     | key                    | key_len | ref                   | rows | filtered | Extra         |
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+----------------+
|  1 | SIMPLE      | c     | ALL    | PRIMARY                           | NULL                    | NULL    | NULL                  |   16 |   100.00 | Using filesort |
|  1 | SIMPLE      | fc    | ref    | PRIMARY,fk_film_category_category | fk_film_category_category | 1       | sakila.c.category_id  |    9 |   100.00 | Using index    |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY                           | PRIMARY                   | 2       | sakila.fc.film_id     |    1 |   100.00 | Using index    |
|  1 | SIMPLE      | i     | ref    | PRIMARY,idx_fk_film_id            | idx_fk_film_id            | 2       | sakila.fc.film_id     |    2 |   100.00 | Using index    |
|  1 | SIMPLE      | r     | ref    | PRIMARY,idx_fk_inventory_id       | idx_fk_inventory_id       | 3       | sakila.i.inventory_id |    1 |   100.00 | Using index    |
|  1 | SIMPLE      | p     | ref    | fk_payment_rental                 | fk_payment_rental         | 5       | sakila.r.rental_id    |    1 |   100.00 | Using where    |
+----+-------------+-------+--------+-----------------------------------+---------------------------+---------+-----------------------+------+----------+----------------+
6 rows in set, 1 warning (0.00 sec)

mysql>

A lot better, isn't it? And faster ;)

BTW: More about EPLAIN EXTENDED here.

Random selection, with a bias ...

Say you want to randomly select your employee of the month, but not so randomly, better, you'd like to give your best employees a bigger chance to be selected based on their rating.
This is just an example, you could be randomly displaying ads from your customers, but giving an higher chance to be displayed to those who are paying more, there can be a million other example, but I hope you got the sense of this.
In other terms this means to add some skew to your data, so that it's distribution is no more uniform.
Apart from the statistical implications of this, let's see the sql in action, I'll be using Firebird's Employee sample database, first of all we'll add a column to the employee table, holding the employee rating, then set the rating to 5 for employee 2, 10 for employee 4, 15 for employee 5, 1 for all other employees.
Now that we have rated each employee we need the magic to have them randomly selected with a bias ...
This is accomplished with a "sequence table" which will do just one thing, hold a list of numbers starting from the minimum rating value to the max rating (you'll probably want to preload it with a lot of numbers starting from 1).
Here is the sql:

CREATE TABLE CONSECUTIVE_NUMBER(
NUM Integer NOT NULL,
CONSTRAINT PK_CONSECUTIVE_NUMBER_1 PRIMARY KEY (NUM)
);

And load it:

insert Into Consecutive_number (num) Values (1);
Insert Into Consecutive_number (num) Values (2);
Insert Into Consecutive_number (num) Values (3);
Insert Into Consecutive_number (num) Values (4);
Insert Into Consecutive_number (num) Values (5);
Insert Into Consecutive_number (num) Values (6);
Insert Into Consecutive_number (num) Values (7);
Insert Into Consecutive_number (num) Values (8);
Insert Into Consecutive_number (num) Values (9);
Insert Into Consecutive_number (num) Values (10);
Insert Into Consecutive_number (num) Values (11);
Insert Into Consecutive_number (num) Values (12);
Insert Into Consecutive_number (num) Values (13);
Insert Into Consecutive_number (num) Values (14);
Insert Into Consecutive_number (num) Values (15);
Insert Into Consecutive_number (num) Values (16);
Insert Into Consecutive_number (num) Values (17);
Insert Into Consecutive_number (num) Values (18);
Insert Into Consecutive_number (num) Values (19);
Insert Into Consecutive_number (num) Values (20);

Now we are ready for the magic, we'll be joining our employee table with this sequence table, generating a specific cross join which will hold as many rows for each employee as it's rating, thus, when doing a random select on this data there will be a bigger chance to choose some employees, i.e. employee number 5 will have 15 times the chance to be selected than employee number 8 and 3 times the chances of employee number 2.
Here is the query, embedded in a view:

CREATE VIEW
RANDOM_SKEWED_EMPLOYEE (EMP_NO)
AS
/* write select statement here */
SELECT
FIRST 1
a.EMP_NO
FROM
EMPLOYEE a
INNER JOIN
CONSECUTIVE_NUMBER c
ON
c.NUM BETWEEN 1 AND a.RATING
ORDER BY
rand();

The join clause does the magic, as I said.
Now extract a small sample:

24
5
36
85
5
2
12
24
144
5
5
20
94
5
4
127
5
94
85
4
2
34
136
4
9

Let's see it's distribution, even on such a small sample you'll see that those 3 employees appear more often than others and with more or less the expected relative ratio:

Emp_no Times
2 5
4 9
5 15
9 4
14 1
20 1
28 2
29 2
36 1
37 1
52 2
71 1
72 1
83 1
113 1
114 1
118 2
121 1
127 1
138 2
144 1

Of course a larger sample will be closer to the desired result.
This example is buit on Firebird, but it should work in any database.
HTH

Note that PostgreSQL's generate_series() instruction might help a lot ;)

Has them all

A question that pops up frequently on Devshed forums is "How can I get all products that are available in Red and Green colors?" or "How can I find out which customers bought this book and that CD?", solution is simple and I'll provide an example here, it can be made more complicate at your option, but it all boils down to a where and an having condition.
Say we have a table that lists all products and the colors in which those products are available:

CREATE TABLE PRODUCT_COLORS(
  PRODUCT_CODE CHAR(5) NOT NULL,
  COLOR_CODE CHAR(1) NOT NULL,
  CONSTRAINT PRODUCT_COLORS_PK PRIMARY KEY (PRODUCT_CODE,COLOR_CODE)
); 

Data looks like:

Code:

XXXXX                R
XXXXX                B
YYYYY                Y
YYYYY                G
ZZZZZ                G
ZZZZZ                R   

First column is the product code, second column is the color code.
Say we want to know which products are available in G(reen) and R(ed), the query is simple, we'll list all products which do have the Red or Green option and then filter out all those that don't have both, getting the desired result (in this case product 'ZZZZZ')
See it in action:

SELECT a.PRODUCT_CODE
FROM PRODUCT_COLORS a
WHERE a.COLOR_CODE IN ('R', 'G')
GROUP BY a.PRODUCT_CODE
HAVING COUNT(a.PRODUCT_CODE) = 2 

See where I implemented the two conditions? One in the where clause and the second pass to filter out all products which don't have both in the having clause.
Example is built on Firebird, but should work in MySQL, PostgreSQL or any other mainstream database too.

Data load speed test

I've run some data load tests with various databases using DBMonster, so connecting to databases through JDBC on a WindowsXP personal computer.
Here are the results, in both cases I loaded 100 rows in the parent table and 1000 in the child table, with foreign keys enabled.


Firebird 2.1 with Jaybird 2.1.3 and DBMonster 1.0.3 (And Java .6)

Table structure is:

CREATE TABLE GUYS(
GUY_ID Integer NOT NULL,
GUY_NAME Varchar(45) NOT NULL,
CONSTRAINT PK_GUYS PRIMARY KEY (GUY_ID)
);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON GUYS TO SYSDBA WITH GRANT OPTION;

CREATE TABLE BADS_ATTRIBUTES(
ATTRIBUTE_ID Integer NOT NULL,
GUY_ID Integer NOT NULL,
ATTRIBUTE_NAME Varchar(45) NOT NULL,
CONSTRAINT PK_BADS_ATTRIBUTES PRIMARY KEY (ATTRIBUTE_ID,GUY_ID)
);
ALTER TABLE BADS_ATTRIBUTES ADD CONSTRAINT FK_BADS_ATTRIBUTES_1
FOREIGN KEY (GUY_ID) REFERENCES GUYS (GUY_ID) ON UPDATE CASCADE ON DELETE CASCADE;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON BADS_ATTRIBUTES TO SYSDBA WITH GRANT OPTION;


D:\dbmonster-core-1.0.3\bin>dbmonster --grab -t guys bads_attributes -o d:/fireb
ird_2_1_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 22:43:03,203 INFO SchemaGrabber - Grabbing schema from database. 2 tables to grab.
2008-05-23 22:43:03,265 INFO SchemaGrabber - Grabbing table GUYS. 50% done.
2008-05-23 22:43:03,359 INFO SchemaGrabber - Grabbing table BADS_ATTRIBUTES. 100% done.
2008-05-23 22:43:03,359 INFO SchemaGrabber - Grabbing schema from database comp
lete.

D:\dbmonster-core-1.0.3\bin>dbmonster -s d:/firebird_2_1_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 22:49:32,828 INFO DBMonster - Let's feed this hungry database.
2008-05-23 22:49:32,984 INFO DBCPConnectionProvider - Today we are feeding: Fir
ebird 2.1 Beta 2=WI-T2.1.0.16780 Firebird 2.1 Beta 2/tcp (xxx)/P10 W
I-T2.1.0.16780 Firebird 2.1 Beta 2=WI-T2.1.0.16780 Firebird 2.1 Beta 2/tcp (pm-7
071b5d42629)/P10
2008-05-23 22:49:33,187 INFO Schema - Generating schema .
2008-05-23 22:49:33,187 INFO Table - Generating table .
2008-05-23 22:49:33,187 INFO Table - Generating table .
2008-05-23 22:49:33,359 INFO Table - Generation of table finished.
2008-05-23 22:49:39,375 INFO Table - Generation of table finished.
2008-05-23 22:49:39,375 INFO Schema - Generation of schema finished.
2008-05-23 22:49:39,375 INFO DBMonster - Finished in 6 sec. 547 ms.

D:\dbmonster-core-1.0.3\bin>

The same with MySQL 5.1.23 Connector/J 5.1.6 (InnoDB tables of course as I wanted to have FK)

Table structure is:

DROP TABLE IF EXISTS `test`.`guys`;
CREATE TABLE `test`.`guys` (
`guy_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_name` varchar(45) NOT NULL,
PRIMARY KEY (`guy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`bads_attributes`;
CREATE TABLE `test`.`bads_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_bads_attributes_1` (`guy_id`),
CONSTRAINT `FK_bads_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8;

D:\dbmonster-core-1.0.3\bin>dbmonster --grab -t guys bads_attributes -o d:/mysql
_5_1_23_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 22:59:40,515 INFO SchemaGrabber - Grabbing schema from database. 2 tables to grab.
2008-05-23 22:59:40,671 INFO SchemaGrabber - Grabbing table guys. 50% done.
2008-05-23 22:59:40,703 INFO SchemaGrabber - Grabbing table bads_attributes. 100% done.
2008-05-23 22:59:40,703 INFO SchemaGrabber - Grabbing schema from database complete.

D:\dbmonster-core-1.0.3\bin>dbmonster -s d:/mysql_5_1_23_schema.xml

D:\dbmonster-core-1.0.3\bin>rem Batch file to run dbmonster under Windows

D:\dbmonster-core-1.0.3\bin>rem Contributed by Peter De Bruycker
2008-05-23 23:00:02,531 INFO DBMonster - Let's feed this hungry database.
2008-05-23 23:00:02,953 INFO DBCPConnectionProvider - Today we are feeding: MyS
QL 5.1.23-rc-community
2008-05-23 23:00:03,093 INFO Schema - Generating schema .
2008-05-23 23:00:03,093 INFO Table - Generating table .
2008-05-23 23:00:03,125 INFO Table - Generating table .
2008-05-23 23:00:12,812 INFO Table - Generation of table finished.
2008-05-23 23:00:49,000 INFO Table - Generation of table finished.
2008-05-23 23:00:49,000 INFO Schema - Generation of schema finished.
2008-05-23 23:00:49,000 INFO DBMonster - Finished in 14 sec. 187 ms.

D:\dbmonster-core-1.0.3\bin>

The difference is quite large!
You can compare my results to those obtained by the SQLite team, hope that these numbers make sense to you.

I'll try with PostgreSQL too, just don't know when

Two basic indexing tips ...

Here are two basic tips for proper indexing ...

1. Don't mess with datatypes, too often people refer to an attribute defining it as one datatype in a table and as another in different tables, this actually prevents index usage in joins (forget about FKs for this time ;)) See an example here. You could declare a function based index as a workaround, but why don't we all try to make it right?
   
2. Put indexes where the database can really use them, if a table is to be fully scanned anyway, it's indexes are unlikely to be used, unless you can compare those index entries with other indexes on tables that won't be fully scanned. Ordering is another game ;). See here for an example.

Easy, isn't it? But these mistakes are still very common ...

Shoot in the foot

I've just finished reading two recent blog posts about new query optimizations in the upcoming MySQL 6.0, it's all fine and dandy but ...
Looking at Correlated semi-join subqueries and PostgreSQL by S. Petrunia we can read something like

Quote:

The first thing we did was to take a look at PostgreSQL as it is easily available and seems to have at least decent subquery handling (or even better than decent, I have not heard much complaints).

Does this mean their benchmark is PostgreSQL? I mean, Oracle and SQLServer are easily available too ... not to mention the comparison any reader can see a few lines below between MySQL's and PostgreSQL's explain plans.

The other amusing read is New optimizer features in MySQL 6.0 again by Sergey where you can find an interesting speed comparison which (might) boil down to

Server version Wallclock time # of reads
MySQL 5.1 12 min 9,001,055
MySQL 5.2 1.8 sec 153,008
MySQL 5.2 no_semijoin 25 sec 7,651,215
PostgreSQL 8.2.5 0.1 sec 2,413

Very interesting!

But anyone interested in query optimization should take the time to check the whole paper, extremely informative (and much of it's content is good for any database too)!!!

Ok, now LET THE FLAME WAR START ;-)

BTW MySQL 5.1 is not in production currently, while PostgreSQL 8.2.5 has just been superseded by the new PostgreSQL 8.3.1

Fasten your seatbelts ...

... or how to safely run data manipulation statements in your database.
Reading posts on Devshed's forums I sometime notice people doing maintenance work on their data without any safety net apart from occasional ages old backups ;-).
Anyway I think there's no need for a restore if you just issued the wrong update query, I mean, transactions are here for this, it's just a matter of educating people ...
Say you have a database structure like this

CREATE TABLE `test`.`users` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB; 

and

CREATE TABLE `test`.`agent` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `actiontime` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_agent_1` FOREIGN KEY `FK_agent_1` (`id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
ENGINE = InnoDB;

not very nice, it's just an example, but notice that I'm also using real foreign keys (real because these are InnoDB tables).
That's because of the many database engines only InnoDB supports transactions (BDB used to support them but has been discontinued and Falcon is not ready for prime time).

Now let's see how this tables content looks like

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | f        |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:21:44 |
+----+---------------------+
3 rows in set (0.00 sec)

Nothing much really, now, say we want to change the actiontime of agent 'f', but we are unshure of the synthax and we don't want to damage our data, that's how we should do:

mysql> set autocommit='OFF';
Query OK, 0 rows affected (0.00 sec)

Actually we have just fastened our seatbelts!!! Autocommit is off and the database will make permanent changes on request only, now on with the query

mysql> update agent a inner join users u on a.id = u.id set a.actiontime=now() where
u.username = 'f';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Looks ok, but let's check what's really happened

mysql> select * from agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:27:07 |
+----+---------------------+
3 rows in set (0.00 sec)

Ok, we updated the right row!
At the same time users are querying the database, but, as we haven't committed work, they see the "old" data (note, this depends on transaction isolation level)

mysql> select * from test.agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:21:44 |
+----+---------------------+
3 rows in set (0.00 sec)

You see? But, as we are satisfied with changes, it's time to commit work, to actually make those changes permanent.

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

At this time users other than the one which actually changed data will start seeing the updated version, see it by yourself

mysql> select * from test.agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:21:44 |
+----+---------------------+
3 rows in set (0.00 sec)
commit by the other user happened between
these selects
mysql> select * from test.agent;
+----+---------------------+
| id | actiontime          |
+----+---------------------+
|  1 | 2008-03-16 11:21:38 |
|  2 | 2008-03-16 11:21:41 |
|  3 | 2008-03-16 11:27:07 |
+----+---------------------+
3 rows in set (0.00 sec)

This was an easy case, but transactions are a safety net because you can rollback changes, see it in action

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | f        |
+----+----------+
3 rows in set (0.00 sec)

mysql> delete from users;
Query OK, 3 rows affected (0.00 sec)

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

 :eek: I just deleted all my precious users!!!

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

mysql> select * from users;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | f        |
+----+----------+
3 rows in set (0.00 sec)

But I had my safety net :-D

Here you can find the thread that prompted me to write this post.

I seem to hit a bug (severe or not) every time I post, this is no exception, see bug 35318, but again and again MySQL's support team showed it's dedication, solving and closing it in less than 8h!!!

Simulating procedural logic

Sometimes I see people having great difficulties in describing how to fetch data for a report.
They are unable to reason by sets and tend to describe things in procedural terms.
Here I'm posting a small example of how you can write a query that reproduces that procedural reasoning and lets the optimizer do the work of translating it into efficient SQL.
Say someone has a table structure like this, a main table named guys holding their id and name and two tables bads_attributes and goods_attributes, if you are a bad guy your attributes will be in the bads_attributes table and vice versa.
Looks ugly? It is, but you'll find it around, sooner or later :-(
And it's not even the worst case scenario, confronted with a similar requirement I've heard that stored procedures where proposed as the ideal solution.
The table structure:

DROP TABLE IF EXISTS `test`.`guys`;
CREATE TABLE  `test`.`guys` (
`guy_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_name` varchar(45) NOT NULL,
PRIMARY KEY (`guy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`bads_attributes`;
CREATE TABLE  `test`.`bads_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_bads_attributes_1` (`guy_id`),
CONSTRAINT `FK_bads_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`goods_attributes`;
CREATE TABLE  `test`.`goods_attributes` (
`attribute_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guy_id` int(10) unsigned NOT NULL,
`attribute_name` varchar(45) NOT NULL,
PRIMARY KEY (`attribute_id`,`guy_id`),
KEY `FK_goods_attributes_1` (`guy_id`),
CONSTRAINT `FK_goods_attributes_1` FOREIGN KEY (`guy_id`) REFERENCES `guys` (`guy_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Now let's retrieve those attributes with a query that mimics the logic described above

SELECT
 g.guy_id,
 g.guy_name,
 CASE
  WHEN EXISTS (SELECT 0 FROM bads_attributes b WHERE b.guy_id = g.guy_id)
   THEN (SELECT group_concat(b.attribute_name separator ', ') FROM bads_attributes b WHERE b.guy_id = g.guy_id GROUP BY b.guy_id)
  WHEN EXISTS (SELECT 0 FROM goods_attributes a WHERE a.guy_id = g.guy_id)
   THEN (SELECT group_concat(a.attribute_name separator ', ') FROM goods_attributes a WHERE a.guy_id = g.guy_id GROUP BY a.guy_id)
  ELSE 'no attributes for this guy'
 END right_attributes
FROM
 guys g 

This goes after the reasoning described above, if you are found in the bads_attributes then your data is retrieved from there, the same for goods_attributes.
Output is

+--------+----------+----------------------------+
| guy_id | guy_name | right_attributes           |
+--------+----------+----------------------------+
|      1 | Paolo    | Fichissimo                 |
|      2 | Carlo    | Fico                       |
|      3 | Ciccio   | Ugly, Uglier               |
|      4 | Bender   | Ugliest                    |
|      5 | New kid  | no attributes for this guy |
+--------+----------+----------------------------+
5 rows in set (0.02 sec)

It's explain plan is

+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
| id | select_type        | table | type | possible_keys         | key         | key_len | ref           | rows | Extra       |
+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
|  1 | PRIMARY            | g     | ALL  | NULL                  | NULL         | NULL    | NULL          |    5 |             |
|  5 | DEPENDENT SUBQUERY | a     | ref  | FK_goods_attributes_1 | FK_goods_attributes_1 | 4       | test.g.guy_id |    1 | Using where |
|  4 | DEPENDENT SUBQUERY | a     | ref  | FK_goods_attributes_1 | FK_goods_attributes_1 | 4       | test.g.guy_id |    1 | Using index |
|  3 | DEPENDENT SUBQUERY | b     | ref  | FK_bads_attributes_1  | FK_bads_attributes_1  | 4       | test.g.guy_id |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | ref  | FK_bads_attributes_1  | FK_bads_attributes_1  | 4       | test.g.guy_id |    1 | Using index |
+----+--------------------+-------+------+-----------------------+-----------------------+---------+---------------+------+-------------+
5 rows in set (0.00 sec)

Well, it could be worse, note that I'm using InnoDB tables and I've declared foreing keys, whose indexes are picked up by the optimizer.

Let's a more SQLish version of this query

SELECT
 g.guy_id,
 g.guy_name,
 COALESCE(
  group_concat(b.attribute_name separator ', '),
  group_concat(a.attribute_name separator ', ')
 ) right_attributes
FROM 
 guys g LEFT OUTER JOIN bads_attributes b 
  ON g.guy_id = b.guy_id
 LEFT OUTER JOIN goods_attributes a 
  ON g.guy_id = a.guy_id
GROUP BY
 g.guy_id,
 g.guy_name 

Output is the same

+--------+----------+---------------------------+
| guy_id | guy_name | right_attributes          |
+--------+----------+---------------------------+
|      1 | Paolo    | Fichissimo                |
|      2 | Carlo    | Fico                      |
|      3 | Ciccio   | Ugly, Uglier              |
|      4 | Bender   | Ugliest                   |
|      5 | New kid  | NULL                      |
+--------+----------+---------------------------+
5 rows in set (0.00 sec)

And the explain plan

+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
| id | select_type | table | type | possible_keys         | key  | key_len | ref           | rows | Extra          |
+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
|  1 | SIMPLE      | g     | ALL  | NULL                  | NULL  | NULL    | NULL          |    5 | Using filesort |
|  1 | SIMPLE      | b     | ref  | FK_bads_attributes_1  | FK_bads_attributes_1  | 4       | test.g.guy_id |    1 |                |
|  1 | SIMPLE      | a     | ref  | FK_goods_attributes_1 | FK_goods_attributes_1 | 4       | test.g.guy_id |    1 |                |
+----+-------------+-------+------+-----------------------+-----------------------+---------+---------------+------+----------------+
3 rows in set (0.00 sec)

Well it looks certainly better, but there's a nasty filesort (this is a very small data set) which should be checked against a much larger dataset.

Doing the same on Firebird 2.1 beta 2 (which supports LIST() a function similar to MySQL's group_concat()) leads to:

SELECT
 g.guy_id,
 g.guy_name,
 CASE
  WHEN EXISTS (SELECT 0 FROM bads_attributes b WHERE b.guy_id = g.guy_id)
   THEN (SELECT CAST(list(b.attribute_name) AS varchar(5000)) FROM bads_attributes b WHERE b.guy_id = g.guy_id 
GROUP BY b.guy_id
)
  WHEN EXISTS (SELECT 0 FROM goods_attributes a WHERE a.guy_id = g.guy_id)
   THEN (SELECT CAST(list(a.attribute_name) AS varchar(5000))  FROM goods_attributes a WHERE a.guy_id = g.guy_id 
GROUP BY a.guy_id
)
  ELSE 'no attributes for this guy'
 END right_attributes
FROM
 guys g 

and

SELECT
 g.guy_id,
 g.guy_name,
 CAST(
  COALESCE(
   list(b.attribute_name),
   list(a.attribute_name)
 ) AS varchar(5000)) right_attributes
FROM 
 guys g
LEFT OUTER JOIN bads_attributes b 
 ON g.guy_id = b.guy_id
LEFT OUTER JOIN goods_attributes a 
 ON g.guy_id = a.guy_id
GROUP BY
 g.guy_id,
 g.guy_name 

Note that both queries need an explicit cast as list's results in Firebird are blobs.
The respective explain plans show that the set oriented one is better.

Prepare time: 00:00:00.
Field #01: GUYS.GUY_ID Alias:GUY_ID Type:INTEGER
Field #02: GUYS.GUY_NAME Alias:GUY_NAME Type:STRING(45)
Field #03: . Alias:RIGHT_ATTRIBUTES Type:STRING(5000)
PLAN (B INDEX (FK_BADS_ATTRIBUTES_1))
PLAN (B ORDER FK_BADS_ATTRIBUTES_1 INDEX (FK_BADS_ATTRIBUTES_1))
PLAN (A INDEX (FK_GOODS_ATTRIBUTES_1))
PLAN (A ORDER FK_GOODS_ATTRIBUTES_1 INDEX (FK_GOODS_ATTRIBUTES_1))
PLAN (G NATURAL)


Executing...
Done.
116 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 21 index, 5 seq.
Delta memory: 54852 bytes.
Execute time: 00:00:00.

and

Prepare time: 00:00:00.
Field #01: GUYS.GUY_ID Alias:GUY_ID Type:INTEGER
Field #02: GUYS.GUY_NAME Alias:GUY_NAME Type:STRING(45)
Field #03: . Alias:RIGHT_ATTRIBUTES Type:STRING(5000)
PLAN JOIN (SORT (JOIN (G NATURAL, B INDEX (FK_BADS_ATTRIBUTES_1))), A INDEX (FK_GOODS_ATTRIBUTES_1))


Executing...
Done.
66 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 9 index, 5 seq.
Delta memory: 38168 bytes.
Execute time: 00:00:00.

How to sync two tables in MySQL

A question pops out quite often on Devshed forums, "How do I keep table x of my local database in sync with a remote copy?"
Usually replication is the suggested answer, but it might be a little overkill, think of those who just want to push the new product catalogue from a local server to their hosted site? No permanent connection and so on ...
In this case the right tool might be a mix of the new MySQL features, federated tables, extended insert synthax, stored procedures, events, triggers ... quite a fest.
Say you have a local catalogue table which holds the products on sale:

CREATE TABLE `test`.`catalogue` (
  `product_code` CHAR(10) NOT NULL,
  `product_name` VARCHAR(45) NOT NULL,
  `product_desc` VARCHAR(500) NOT NULL,
  `product_weight` DECIMAL NOT NULL,
  `product_colour` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`product_code`)
)

Load it with a sample row

INSERT INTO 
 catalogue 
VALUES
('AA12F', 'Sm. Widget', 'This is a small widget', 5, 'Red'); 

In this sample I'll suppose that a similar table exists in the remote database, no need to check.

First of all we need to be able to issue queries against that table right from this server, we'll use federated tables:

DROP TABLE IF EXISTS `test`.`remote_catalogue`;
CREATE TABLE  `test`.`remote_catalogue` (
  `product_code` CHAR(10) NOT NULL,
  `product_name` varchar(45) NOT NULL,
  `product_desc` varchar(500) NOT NULL,
  `product_weight` decimal(10,0) NOT NULL,
  `product_colour` varchar(45) NOT NULL,
  PRIMARY KEY (`product_code`)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root:nt300jk@127.0.0.1:3306/remote_test/catalogue';
--don't use a privileged user in real life!!! 

Now we have our federated table pointing to the remote server, let's set up the most basic sync, one query will be enough

REPLACE INTO remote_catalogue SELECT * FROM catalogue 

A quick test:

mysql> use remote_test
Database changed
mysql> select * from catalogue;
+--------------+--------------+------------------------+----------------+-------
---------+
| product_code | product_name | product_desc           | product_weight | produc
t_colour |
+--------------+--------------+------------------------+----------------+-------
---------+
| AA12F        | Sm. Widget   | This is a small widget |              5 | Red
|
+--------------+--------------+------------------------+----------------+-------
---------+
1 row in set (0.01 sec)

mysql>

After a while data in our local database changes, a new product is added and the existing one is updated

INSERT INTO 
 catalogue 
VALUES
 ('AB12G', 'Lg. Widget', 'A large widget', 34, 'blue');
 
UPDATE 
 catalogue 
SET 
 product_weight = 4 
WHERE 
 product_code = 'AA12F'; 

we are ready to push the changes to remote site with the query used before, reissueing the REPLACE leads to this on the remote server

mysql> select * from catalogue;
+--------------+--------------+------------------------+----------------+-------
---------+
| product_code | product_name | product_desc           | product_weight | produc
t_colour |
+--------------+--------------+------------------------+----------------+-------
---------+
| AA12F        | Sm. Widget   | This is a small widget |              4 | Red
    |
| AB12G        | Lg. Widget   | A large widget         |             34 | blue
    |
+--------------+--------------+------------------------+----------------+-------
---------+
2 rows in set (0.00 sec)

So now the two tables hold the same values!
Enough for now! I'll show you the rest later

BTW, while writing this I filed two bug reports (34973 and 34971) hope those will turn into errors on my side ...

MySQL's support guys took care of the bug reports, the outcome is:

Bug #34973 is not a bug (it's a duplicate of closed bug 25511 which says that's it's fine for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE to fail with federated tables), however bug 25511 suggests that REPLACE doesn't work too, but, as you can see it works fine.

Bug #34971 is verified, from Miguel's comments it looks like it's fixed in 5.1.24 (not released yet)