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 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]
