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.
Labels: database, firebird, mysql, procedural reasoning, set oriented
