Curt Zirzow wrote:
* Thus wrote Lenar Lhmus:
Mattias Thorslund wrote:
Though, (regardless of SQL database platform) in a case like this, itNo, I think you are wrong. There was GROUP BY pics.id so it won't return
MAY be both more straightforward and efficient to use two separate
queries for the pictures and comments, given the one-to-many cardinality
between them. The above statement causes redundant data to be returned
- it duplicates the pics record for each new comment. If there are many
large columns in the pics table and many comments to each record there's
a lot of redundant data in a query like that.
duplicate data. And probable overhead is eliminated too I think - DBserver
will optimize and will join data from pic_comments only once for each
unique pics.id.
The results will be something like:
id submitter count pic_id comment 1 joe 4 1 comment1 1 joe 4 1 comment2 1 joe 4 1 comment3 1 joe 4 1 comment4 2 john 0 2 NULL 3 jim 5 3 comment6 3 jim 5 3 comment7 3 jim 5 3 comment8 3 jim 5 3 comment9 3 jim 5 3 comment10
Exactly.
To make it REALLY obvious, I'll point out what parts of the data set that are redundant by replacing them with stars, using Curt's example above:
id submitter count pic_id comment 1 joe 4 1 comment1 * * * * comment2 * * * * comment3 * * * * comment4 2 john 0 2 NULL 3 jim 5 3 comment6 * * * * comment7 * * * * comment8 * * * * comment9 * * * * comment10
The actual table structure from the original poster (an ex-namesake of mine*) would result in seven columns with redundant data, i.e. where data is needlessly repeated for every picture that has two or more comments.. It wouldn't be hard to find worse real-world examples.
pics table: | id | submitter | time | date | category |
pic_comments table: | pic_id | comment |
- Mattias Thorslund
(*) My last name used to be Jönsson, too. Americans just can't pronounce it right.
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
