FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Twitter like follow system with MySQL - Help





imagefree
I am trying to make a follow system where you can see latest updated from all those users whom you follow.

The problems here is that i do not want to keep a limit on the number of users you follow (or the limit will be very high like 5000 or more). Under such case the Queries like following would be very slow (tell me if i am wrong).

Code:
"SELECT * FROM updates WHERE userid IN( $comma_separated_ids_of_users ) $sorting $limit "

"SELECT * FROM updates WHERE userid = ( SELECT userid FROM follows WHERE followerid = $this_user_id ) $sorting $limit "


I want to make twitter/tumbler/'Facebook News Feed' like follow system and i want it to work as efficient as that of those services. How it could be done (using simple querying or any advance technique).
jmraker
I'm guessing the 1st one would be better. I wouldn't store the comma separated list in the database all in one field unless you want to use a "text" (64k) field. I would make the login program setup an array of followers and follows in the session so all the data that's looked up frequently doesn't need to come from the database on every request. If they add/remove a follow you can either modify that part of the session or call a function to refresh their session from the database.
imagefree
Comma separated list is used inside IN() mean this is taken as an array of user IDs while searching. Each ID is searched and compared separately.


Quote:
I would make the login program setup an array of followers and follows in the session so all the data that's looked up frequently doesn't need to come from the database on every request. If they add/remove a follow you can either modify that part of the session or call a function to refresh their session from the database.


I have no problem with the list of follows. Whether it is 5K or 5M i do not care. So there is no point of storing the whole list in session. What i am worried about is that such a long list of follows will make fetching updates of those follows very slow.

When you see Updates from friends on your homepage of twitter account, it updates after every few seconds. Means twitter has no problem with such queries and so reloads updates every few seconds for its 100s of thousands of online users.
Fire Boar
Queries like that aren't very slow at all. I'd recommend optimizing by using a join rather than IN though.

Code:
SELECT u.* FROM updates AS u INNER JOIN follows AS f ON u.userid = f.userid WHERE f.followerid = '$this_user_id' $sorting $limit
mahirh
imagefree wrote:
Comma separated list is used inside IN() mean this is taken as an array of user IDs while searching. Each ID is searched and compared separately.


Quote:
I would make the login program setup an array of followers and follows in the session so all the data that's looked up frequently doesn't need to come from the database on every request. If they add/remove a follow you can either modify that part of the session or call a function to refresh their session from the database.


I have no problem with the list of follows. Whether it is 5K or 5M i do not care. So there is no point of storing the whole list in session. What i am worried about is that such a long list of follows will make fetching updates of those follows very slow.

When you see Updates from friends on your homepage of twitter account, it updates after every few seconds. Means twitter has no problem with such queries and so reloads updates every few seconds for its 100s of thousands of online users.

i will make it simple , twitter uses ajax and juavascript frameworks like jquery and such stuff , even if doing it fully server side is not hard to code , it can have much bigger consequences which is something we dont want in such a reliable system like twitter or face book , but as a small project , i dont think it will be very slow
Related topics
PHP/Mysql - beginner!
Setup php,mysql and others...
Mod Projects lots of info
MySQL HELP!!
php HELP!!!
Need help booting from USB (Dell BIOS)
PHP + MySQL Help
[RESOLVED] tar.gz help
Crazy language/character set in MYSQL! HELP, PLEASE!!!
Free support ticket system - eTicket
mySQL help needed please.
mySQL help needed, any other good database thing?
Mysql and PHP separately to work on windows 7
Follow me on Twitter. Is my HTML Correct
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.