On 6 Mai, 13:09, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> cvh@LE wrote:
> > @Jerry : As said, I am neither the admin nor have I the privileges to
> > normalize the db. Furthermore normalizing this column would create
> > more overhead than performance gain could be won.
>
> Not having the authority is one thing. *Claiming it will create more
> overhead is another - and probably FALSE.
>
> Very seldom have I actually SEEN a degradation of performance when
> normalizing a database. *And virtually never when normalizing a column
> with multiple values. *That almost always improves performance across
> the board. *And it definitely improves performance on this particular query.
>
> It was true that normalization hurt database performance - 25 years ago.
> * But things have come a long ways since then, and RDBMS's typically do
> better with normalized data than not.
>
> Of course, there are always exceptions. *But until you actually take
> performance benchmarks with your statements and your data, you can't say
> that.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

@Jerry I absolutely agree with you on normalisation. And I, too, have
seldomly (although I do have) experienced that normalization is a
performance downer.

However, the application-database which makes use of this column-
containing-a-set problem is normalized already, except for this column
which happens to contain data similar to comments. Comments mostly are
formulated as sentences, which happens to be nothing more than an
ordered space separated set. This column contains exactly the same. I
doubt anybody would normalize a short one-sentence comment field in a
table holding, let's say, additional remarks (3NF) given as part of
responses (2NF) by respondents (1NF) in surveys.

Anyway, the whole question of mine targeted a function allowing me to
retrieve the nth element in a set (I agree, the word occurence was the
wrong choice - but, hey, English isnt my native tongue).

To illustrate the problem a bit:

The column in question containing the set could itself be the result
of an aggregate function :

imagine :
mysql> create table test1 (pk int(1),col1 varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(pk,col1) values (1,"a"),(2,"b"),(1,"c"),
(2,"e"),(1,"f"),(2,"g");
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from test1;
+------+------+
| pk | col1 |
+------+------+
| 1 | a |
| 2 | b |
| 1 | c |
| 2 | e |
| 1 | f |
| 2 | g |
+------+------+
6 rows in set (0.01 sec)

mysql> select pk,group_concat(col1 separator ",") from test1 group by
1;
+------+----------------------------------+
| pk | group_concat(col1 separator ",") |
+------+----------------------------------+
| 1 | a,c,f |
| 2 | b,e,g |
+------+----------------------------------+
2 rows in set (0.00 sec)

mysql> select pk,metadaten.element_in_set(group_concat(col1 separator
","),",",2) 2ndElement from test1 group by 1;
+------+------------+
| pk | 2ndElement |
+------+------------+
| 1 | c |
| 2 | e |
+------+------------+
2 rows in set (0.00 sec)

mysql>