cvh@LE wrote:
> 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>
>
>
>
>

Yes, I understand your design. But there are no functions to retrieve
information form a set because the set itself violates 1NF. And if the
database is normalized properly, there is no need.

And yes, I would normalize such a field in such circumstances.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================