SQL query for most recent items

[Abmar Barros]

Hi,

We've been trying to improve the performance of the most recent items per channel SQL query. I just submitted a pull request for that:  

--
Abmar Barros
MSc in Computer Science from the Federal University of Campina Grande - www.ufcg.edu.br
OurGrid Team Leader - www.ourgrid.org

Buddycloud Dev - www.buddycloud.org
Paraíba - Brazil

[Abmar Barros]

Sorry guys, message went unfinished. Again:

We've been trying to improve the performance of the most recent items per channel SQL query, and I just submitted a pull request for that: https://github.com/buddycloud/buddycloud-server/pull/116

Based on: 
http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group

http://stackoverflow.com/questions/1153738/mysql-limit-inside-group

And here's our current schema:
https://github.com/buddycloud/buddycloud-server/blob/develop/postgres/install.sql

We got around 10% performance improvement by doing the UNION ALL of all subqueries. However, we still think we can get a better performance out of it. Do you think we can do better without changing the schema?

[Simon Tennant]

To be clear:

We're trying to populate clients with new messages AND avoid flooding the client with hundreds of posts from a very active channel.

"Send me all posts since <latest message on client> for all the channels I follow, but not more than 10 posts per channel that I follow."

The aim being that a device would have a rough idea of how many posts it needs to draw a screen, and subsequent posts for that channel can then be pulled in.

Secondarily, we need to query for unread counters per channel.

S.

--
You received this message because you are subscribed to the Google Groups "buddycloud-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to buddycloud-de...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Simon Tennant | buddycloud.com | +49 17 8545 0880 | office hours: goo.gl/tQgxP