SQL optimization and normalization

[the-sageman]

After looking over the SQL statements a bit, I realized that FChannel does not use indexes at all. It's not just that, there's also some pretty wieldy SQL statements such as this:

select count (x.id) over(), x.id, x.name, x.content, x.type, x.published, x.updated, x.attributedto, x.attachment, x.preview, x.actor, x.tripcode, x.sensitive from (select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor=$1 and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from activitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note' union select id, name, content, type, published, updated, attributedto, attachment, preview, actor, tripcode, sensitive from cacheactivitystream where actor in (select following from following where id=$1) and id in (select id from replies where inreplyto='') and type='Note') as x order by x.updated desc limit 8 offset $2

Now, I'm not against large SQL statements, but that will be a pretty tough statement to optimize. I'm pretty sure there's lots of opportunities to normalize the database too.

[the-sageman]

Another thing that I noticed is that some tables (for example, reported) are less than ideal, in that example it is because it only lists the first reason that was filled, so a rethinking of the SQL database will be needed regardless

[FChannel0]

Agreed these could be cleaned up.
And yes the report reason needs to be an array or something to not see only the first reported reason.