Complicated Facebook Wall Style Query

[jimmyoneshot]

I'm attempting to replicate something like the wall which Facebook use in the following way:-

 

- users add either videos or photos to my site

- users can then make a post which all logged in users can see. These posts can be simply text or they can have a previously posted video or photo attached to them

- these posts can be commented on by all users

 

My problems are the following:-

 

On the "wall" I need each post to be shown followed by the latest 3 comments on that post HOWEVER if the post has a video or photo attached to it then those top 3 comments need to be the latest 3 comments on that video/photo so the following will be achieved:-

 

Regular post

-regular comment

-regluar comment

-regular comment

 

Post with attached video

-video comment

-video comment

-video comment

 

etc

 

In doing this I have segmented my tables appropriately rather than using types by using tables named as such - videos, videoComments, photos, photoComments, posts, postComments. I've heard this can help performance

 

I've managed to get posts and their top 3 comments using a link suggested by Fenway:-

 

http://explainextended.com/2009/03/06/advanced-row-sampling/

 

But the only way I can think of doing what I want is creating a global comments table for all comments. Here's my query so far. I know it seems heavy but can anyone offer any advice? I've put some comments in to explain my columns

 

SELECT DISTINCT 

@rowtype:='1' AS rowType, //Type of row post or comment
s.id AS rowId, //used as the 'grouper' to group posts and their comments
s.itemTypeId AS oTypeId, //Type of attachment if any

s.id,
s.body, //text of the post
s.date,
s.aboutItemId, //id of the attached item

//Post author details
u1.id AS u1_id,
u1.accountTypeId AS u1_accountTypeId,
u1.username AS u1_username,
u1.name AS u1_name,
u1.surname AS u1_surname,

//Attachment details and attachment author username
COALESCE(p.id, v.id) AS oId,
COALESCE(p.name, v.name) AS oName,
COALESCE(p.body, v.body) AS oBody,
COALESCE(p_user.username, v_user.username) AS oUsername,

FROM z_posts AS s

LEFT JOIN z_users AS u1 ON s.id1 = u1.id
LEFT JOIN z_photos AS p ON s.aboutItemId = p.id AND s_type.itemTypeId = p.itemTypeId 
LEFT JOIN z_users AS p_user ON p.authorId = p_user.id
LEFT JOIN z_videos AS v ON s.aboutItemId = v.id AND s_type.itemTypeId = v.itemTypeId
LEFT JOIN z_users AS v_user ON v.authorId = v_user.id

UNION

SELECT

@rowtype:='2' AS rowType,
l.id2 AS rowId,
NULL AS oTypeId,
  
l.id, 
l.body, 
l.date,
l.id1 AS u1_id,
NULL AS u1_accountTypeId,
NULL AS u1_username,
NULL AS u1_name,
NULL AS u1_surname,
NULL AS aboutItemId,
NULL AS oId,
NULL AS oName,
NULL AS oBody,
NULL AS oUsername,

FROM    (
        SELECT  id2,
                COALESCE(
                (
                SELECT  id
                FROM    z_postComments li
                WHERE   li.id2 = dlo.id2
                ORDER BY
                        li.id2, li.id
                LIMIT 14, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
        FROM    (
                SELECT  DISTINCT id2
                FROM    z_postComments dl
                ) dlo
        ) lo, z_postComments l
WHERE   l.id2 >= lo.id2
        AND l.id2 <= lo.id2
        AND l.id <= lo.mid

ORDER BY rowId

[fenway]

Well, at the very least, you can UNION the results of two seemingly identical queries, one for regular, another for video.

[jimmyoneshot]

Cheers Fenway.

 

I'll see how it goes. Do you think I'm going along the right lines by coalescing the attachment details like I have?

 

I'm trying to get the query to work as fast as possible.

 

The only other option on that part is to have an objects table acting as a base table for all objects and have seperate tables for any extra fields specfic to any type of object which I think you advised me against a little while back.

[fenway]

The "official" design pattern suggests a base table -- but I've never liked that much.

[jimmyoneshot]

Me neither. Mainly because when a user adds an object the system would need to:-

 

1. insert an object

2. get the id of the last inserted object

3. insert the object extra details into the extra detail table

 

rather than just inserting everything in one single query

 

I do see a benefit in this case though as it would save me coalescing all of the common object details as you can see I do in my current query. This may not mean much though I suppose it depends on how detrimental coalesce is to the speed of a query, which I'm not sure on 

[fenway]

It's always easy to make things "more normalized" later.