Re: MySQL Denormalized
Jan Gomes wrote:
Pardon me, i think I din't express myself properly.
I did not only combining data elements.
My structure was:
Table_1{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
}
Table_2{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
}
Being a relationship between table_1 and table_2 1-N.
For each entrance in table_1 it would have among 100-1000 register in table_2.
It would help to know what the relationship was between the two tables:
Is column 1 or 2 of table 1 a foreign key pointing to the primary key of
table 2? (probably not or yoou wouldn't have 100-1000 records in table
2 for each record in table 1.)
So is column 1 or 2 of table 2 a foreign key pointing to id_table1 ?
or what?
After many tests I was with the following structure:
Table{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
COLLUM 3 (TEXT) (UNION COLLUM 1 and 2 of table_2)
}
What it brought me storage and speed benefits.
What I imagined was a caracter to use as separating that it occupied little
space, but seems not to exist.
You are combining two integer fields into one text field!!
It would probably be more efficient to keep them separate integer fields.
Table{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
COLLUM 3 (int) (COLLUM 1 of table_2)
COLLUM 4 (int) (COLLUM 2 of table_2)
}
It might help to know what kind of queries you are doing on this table.
-J
Thanks for all.
==
Atenciosamente,
Jan Gomes - [EMAIL PROTECTED]
A specific character that would occupy less space? You mean like using lower
case instead of upper case? All
characters use the same
amount of space (unless your using Chinese or something).
I don't think reducing the number of columns will help. Usually you take other
performance enhancing measures
first, like
structuring it to use fixed length records. You should probably use InnoDB and
index the fields you normaly
retrieve. Since InnoDB
stores the data with the index, it doesn't need to access the actual table
unless you are pulling data that
isn't indexed.
While denormaliztion is certainly applicable and desired in some case (whoever
saw a database in fifth normal
form!), you are not
denormalzing, your combining data elements. There are so many other things you
can try before you unstructure
your data. If you're
going to unstructure your data, you might as well compress it too. Heck, you
might even look into separating
out the data you don't
search on into a separate compressed table, and have a 1-1 relation. Kind of a search
table and a detail table.
I don't know which response time you are trying to keep to .01 or lower. If
it's end to end, you probably
want to look at your
network. Network latency can be the biggest culprit.
- Original Message -
From: Jan Gomes [EMAIL PROTECTED]
To: mysql mysql@lists.mysql.com
Cc: johnlist [EMAIL PROTECTED]
Sent: Friday, June 30, 2006 3:28 PM
Subject: Re: MySQL Denormalized
John Hicks wrote:
I don't see a question here.
But that won't stop me from giving a little advice :)
It is generally more important to keep things simple (by not
denormalizing) than to try to optimize performance by complicating
things significantly.
Moreover, I can't see how combining several columns into one will
improve performance. I would think it will slow things down whenever you
have to retrieve data, particular if you query against anything in column 3.
And now you say you want to save disk space by compressing the field
separators in your combined column?
Forget it all! Go back to a fully normalized design. If you have
problems, post them here
I kind of disagree on what you said regarding denormalization, but believe me
when I say that I have
experienced a 90% improvement on performance with that.
As I said before, my table has +20 million entries; if it was normalized this
number would be around 20
billion, since it would be a 1 - N relation.
Off course I don't make any selections based on column 3, but only by the
table's keys.
Forget that!!! Runing for normalization would not be viable for me. I need a
response time lower than 0.01 sec.
(and I've been achieving less than that)
However I would like to make a better use of this column's space, once I use
two only characters for separators.
Here's my question: Is there anyway I could minimize that? Is there any
specific character that would occupy
less space?
Once again thank you very much
==
Atenciosamente,
Jan Gomes - [EMAIL PROTECTED]
Jan Gomes wrote:
Hy Guys,
I needed denormalized my table to obtain high performance, but i want best
appropriate the space.
I joint two column (of the JOIN) intro one column with two separadores (# and ;)
Example:
ID | column_1 | column_denormalized
1 | Test | 1#20202;5#1000101;
It has some method to minimize the space(disk space) required for this separadores
? Like some character
that i
can use for minimize the table size?
PS: The table
Re: MySQL Denormalized
John Hicks wrote: I don't see a question here. But that won't stop me from giving a little advice :) It is generally more important to keep things simple (by not denormalizing) than to try to optimize performance by complicating things significantly. Moreover, I can't see how combining several columns into one will improve performance. I would think it will slow things down whenever you have to retrieve data, particular if you query against anything in column 3. And now you say you want to save disk space by compressing the field separators in your combined column? Forget it all! Go back to a fully normalized design. If you have problems, post them here I kind of disagree on what you said regarding denormalization, but believe me when I say that I have experienced a 90% improvement on performance with that. As I said before, my table has +20 million entries; if it was normalized this number would be around 20 billion, since it would be a 1 N relation. Off course I dont make any selections based on column 3, but only by the tables keys. Forget that!!! Runing for normalization would not be viable for me. I need a response time lower than 0.01 sec. (and Ive been achieving less than that) However I would like to make a better use of this columns space, once I use two only characters for separators. Heres my question: Is there anyway I could minimize that? Is there any specific character that would occupy less space? Once again thank you very much == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] Jan Gomes wrote: Hy Guys, I needed denormalized my table to obtain high performance, but i want best appropriate the space. I joint two column (of the JOIN) intro one column with two separadores (# and ;) Example: ID | column_1 | column_denormalized 1 | Test | 1#20202;5#1000101; It has some method to minimize the space(disk space) required for this separadores ? Like some character that i can use for minimize the table size? PS: The table has 20.000.000 of rows with 2 GB data length. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Denormalized
A specific character that would occupy less space? You mean like using lower case instead of upper case? All characters use the same amount of space (unless your using Chinese or something). I don't think reducing the number of columns will help. Usually you take other performance enhancing measures first, like structuring it to use fixed length records. You should probably use InnoDB and index the fields you normaly retrieve. Since InnoDB stores the data with the index, it doesn't need to access the actual table unless you are pulling data that isn't indexed. While denormaliztion is certainly applicable and desired in some case (whoever saw a database in fifth normal form!), you are not denormalzing, your combining data elements. There are so many other things you can try before you unstructure your data. If you're going to unstructure your data, you might as well compress it too. Heck, you might even look into separating out the data you don't search on into a separate compressed table, and have a 1-1 relation. Kind of a search table and a detail table. I don't know which response time you are trying to keep to .01 or lower. If it's end to end, you probably want to look at your network. Network latency can be the biggest culprit. - Original Message - From: Jan Gomes [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Cc: johnlist [EMAIL PROTECTED] Sent: Friday, June 30, 2006 3:28 PM Subject: Re: MySQL Denormalized John Hicks wrote: I don't see a question here. But that won't stop me from giving a little advice :) It is generally more important to keep things simple (by not denormalizing) than to try to optimize performance by complicating things significantly. Moreover, I can't see how combining several columns into one will improve performance. I would think it will slow things down whenever you have to retrieve data, particular if you query against anything in column 3. And now you say you want to save disk space by compressing the field separators in your combined column? Forget it all! Go back to a fully normalized design. If you have problems, post them here I kind of disagree on what you said regarding denormalization, but believe me when I say that I have experienced a 90% improvement on performance with that. As I said before, my table has +20 million entries; if it was normalized this number would be around 20 billion, since it would be a 1 - N relation. Off course I don't make any selections based on column 3, but only by the table's keys. Forget that!!! Runing for normalization would not be viable for me. I need a response time lower than 0.01 sec. (and I've been achieving less than that) However I would like to make a better use of this column's space, once I use two only characters for separators. Here's my question: Is there anyway I could minimize that? Is there any specific character that would occupy less space? Once again thank you very much == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] Jan Gomes wrote: Hy Guys, I needed denormalized my table to obtain high performance, but i want best appropriate the space. I joint two column (of the JOIN) intro one column with two separadores (# and ;) Example: ID | column_1 | column_denormalized 1 | Test | 1#20202;5#1000101; It has some method to minimize the space(disk space) required for this separadores ? Like some character that i can use for minimize the table size? PS: The table has 20.000.000 of rows with 2 GB data length. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Denormalized
Pardon me, i think I din't express myself properly.
I did not only combining data elements.
My structure was:
Table_1{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
}
Table_2{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
}
Being a relationship between table_1 and table_2 1-N.
For each entrance in table_1 it would have among 100-1000 register in table_2.
After many tests I was with the following structure:
Table{
id_table1 (int)
COLLUM 1 (int)
COLLUM 2 (int)
COLLUM 3 (TEXT) (UNION COLLUM 1 and 2 of table_2)
}
What it brought me storage and speed benefits.
What I imagined was a caracter to use as separating that it occupied little
space, but seems not to exist.
Thanks for all.
==
Atenciosamente,
Jan Gomes - [EMAIL PROTECTED]
A specific character that would occupy less space? You mean like using lower
case instead of upper case? All
characters use the same
amount of space (unless your using Chinese or something).
I don't think reducing the number of columns will help. Usually you take
other performance enhancing measures
first, like
structuring it to use fixed length records. You should probably use InnoDB
and index the fields you normaly
retrieve. Since InnoDB
stores the data with the index, it doesn't need to access the actual table
unless you are pulling data that
isn't indexed.
While denormaliztion is certainly applicable and desired in some case
(whoever saw a database in fifth normal
form!), you are not
denormalzing, your combining data elements. There are so many other things
you can try before you unstructure
your data. If you're
going to unstructure your data, you might as well compress it too. Heck, you
might even look into separating
out the data you don't
search on into a separate compressed table, and have a 1-1 relation. Kind of
a search table and a detail table.
I don't know which response time you are trying to keep to .01 or lower. If
it's end to end, you probably
want to look at your
network. Network latency can be the biggest culprit.
- Original Message -
From: Jan Gomes [EMAIL PROTECTED]
To: mysql mysql@lists.mysql.com
Cc: johnlist [EMAIL PROTECTED]
Sent: Friday, June 30, 2006 3:28 PM
Subject: Re: MySQL Denormalized
John Hicks wrote:
I don't see a question here.
But that won't stop me from giving a little advice :)
It is generally more important to keep things simple (by not
denormalizing) than to try to optimize performance by complicating
things significantly.
Moreover, I can't see how combining several columns into one will
improve performance. I would think it will slow things down whenever you
have to retrieve data, particular if you query against anything in column 3.
And now you say you want to save disk space by compressing the field
separators in your combined column?
Forget it all! Go back to a fully normalized design. If you have
problems, post them here
I kind of disagree on what you said regarding denormalization, but believe me
when I say that I have
experienced a 90% improvement on performance with that.
As I said before, my table has +20 million entries; if it was normalized this
number would be around 20
billion, since it would be a 1 - N relation.
Off course I don't make any selections based on column 3, but only by the
table's keys.
Forget that!!! Runing for normalization would not be viable for me. I need a
response time lower than 0.01 sec.
(and I've been achieving less than that)
However I would like to make a better use of this column's space, once I use
two only characters for separators.
Here's my question: Is there anyway I could minimize that? Is there any
specific character that would occupy
less space?
Once again thank you very much
==
Atenciosamente,
Jan Gomes - [EMAIL PROTECTED]
Jan Gomes wrote:
Hy Guys,
I needed denormalized my table to obtain high performance, but i want best
appropriate the space.
I joint two column (of the JOIN) intro one column with two separadores (#
and ;)
Example:
ID | column_1 | column_denormalized
1 | Test | 1#20202;5#1000101;
It has some method to minimize the space(disk space) required for this
separadores ? Like some character
that i
can use for minimize the table size?
PS: The table has 20.000.000 of rows with 2 GB data length.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Denormalized
Hy Guys, I needed denormalized my table to obtain high performance, but i want best appropriate the space. I joint two column (of the JOIN) intro one column with two separadores (# and ;) Example: ID | column_1 | column_denormalized 1 | Test | 1#20202;5#1000101; It has some method to minimize the space(disk space) required for this separadores ? Like some character that i can use for minimize the table size? PS: The table has 20.000.000 of rows with 2 GB data length. Thanks. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Denormalized
Jan Gomes wrote: Hy Guys, I needed denormalized my table to obtain high performance, but i want best appropriate the space. I joint two column (of the JOIN) intro one column with two separadores (# and ;) Example: ID | column_1 | column_denormalized 1 | Test | 1#20202;5#1000101; It has some method to minimize the space(disk space) required for this separadores ? Like some character that i can use for minimize the table size? PS: The table has 20.000.000 of rows with 2 GB data length. I don't see a question here. But that won't stop me from giving a little advice :) It is generally more important to keep things simple (by not denormalizing) than to try to optimize performance by complicating things significantly. Moreover, I can't see how combining several columns into one will improve performance. I would think it will slow things down whenever you have to retrieve data, particular if you query against anything in column 3. And now you say you want to save disk space by compressing the field separators in your combined column? Forget it all! Go back to a fully normalized design. If you have problems, post them here. -J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
