I've been working on this for some hours now and it's getting tiring. I want to get users posts from people I follow and myself, just like twitter does.
So there's a table users_posts that has all users' posts with column user_id to determine who made the post.
Another table users_followers that contains all followers ie. user_id follows following_id
So here's my query:
User ID = 2271
SELECT users_followers.following_id AS following_id, users_posts.id
FROM users_followers, users_posts
WHERE users_posts.user_id = users_followers.following_id
AND (users_followers.user_id =2271)
This query works but the problem is, it's kinda slow. Is there a faster way to do this?
Also, as you can see, I can only get posts from those I follow, and not myself.
Any help?
If I'm understanding your tables properly, I would do this with an explicit JOIN. I'm not sure how much that would speed things up versus the implicit JOIN you're using though.
SELECT following_id, p.id
FROM users_followers f
LEFT JOIN users_posts p
ON (p.user_id = f.following_id)
WHERE f.user_id = 2271
Chances are, adding an index or two to your tables would help.
Using the MySQL EXPLAIN command (just put it in front of your SELECT query) will show the indexes, temporary tables, and other resources that are used for a query. It should give you an idea when one query is faster or more efficient than another.
Your query is fine as written, provided you have properly indexed your tables. At a minimum you need an index on users_posts.user_id and users_followers.following_id.
A query can also be slowed by large numbers of records, even when it is fully indexed. In that case, I find phpmyadmin to be an invaluable tool. From the server page (maybe localhost) select the Status tab to see a wealth of information about how your mysql server is performing and suggestions for how to improve it.