Re: unnormalize db here is more efficient?

[Thufir]

On Tue, 04 Mar 2008 15:04:30 +0100, Nacho Garcia wrote:

 or count the comments on elements_comments table. I guess the last
 option could be very slow with lot of rows because mysql has to scan all
 of them .. and the first option seems to be very fast.


I would certainly want to be able to:

SELECT e.element, c.comment FROM elements, comments AS e, c WHERE 
e.id=c.element_id;


Surely elements.id should be a primary key?  And, comments.element_id 
would be a foreign key?

The only way to be able to get a result like:


elementscomments
=
earth   a comment on the earth
winda comment of the wind
firea comment on fire
water   a comment on water
water   another comment on water
water   yet another comment on water
earth   again, with the earth!



and *not* have problems with redundant data is to decompose into multiple 
tables.  Perhaps an index would speed things up?  There's a calculation 
involved?  I didn't follow what was be calculated.  Perhaps GROUP BY and 
COUNT can be used to do the calculation you want.



-Thufir



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

unnormalize db here is more efficient?

[Nacho Garcia]

Hi, i hope this is the right place for this basic question.
i have a table like this:

TABLE elements
`id_element` INT UNSIGNED NOT NULL ,
`name`  VARCHAR(100),
`date` DATE
...

and other table with the comments of every element.

TABLE elements_comments
`id_element` INT UNSIGNED NOT NULL ,
`comment` TEXT

each element can have a lot of comments, so,
what's more efficient, unnormalize the database and keep track of the amount
of comments (using a trigger fired when a comment is wrote or deleted) such
as

TABLE elements
`id_element` INT UNSIGNED NOT NULL ,
`name`  VARCHAR(100),
`date` DATE,
`comments`  SMALLINT UNSIGNED
...

or count the comments on elements_comments table.
I guess the last option could be very slow with lot of rows because mysql
has to scan all of them .. and the first option seems to be very fast.

can someone help me with that?

thanks a lot