How to avoid this complex query?

Question

I am designing a site like pinterest ( for study ) and I want to know what query I have to do at the homepage to show the user's stream.

I have created these 6 tables:

users
boards
pictures
boards_pictures ( many-to-many table )
followers
comments

And in home, I do this query to get all followers' pictures.

SELECT users.username, pictures.link, comments.comment, boards.title
FROM boards_pictures, pictures, followers, users, comments, boards 
WHERE ( boards_pictures.id_user = followers.id_following ) 
  AND ( boards_pictures.id_picture = pictures.id ) 
  AND ( followers.id_user = $_session['userid'] ) 
  AND ( users.id = followers.id_following )
  AND ( comments.picture_id = pictures.id )
  AND ( boards.id = boards_pictures.boards_id )

Is there a way to avoid this complex query ( a JOIN with 6 tables ) ?

It's a little difficult to answer this without knowing the table structure of each table. — saluce, May 17 '12 at 19:38
That doesn't come close to a complex query (I was revising one a 1000 lines long earlier today) If you need all six tables join to all of them, but do not ever use implicit joins they are very poor programming practice and a SQL antipattern. No sense learning incorrectly, use explicit joins only from now on. — HLGEM, May 17 '12 at 19:40
Why is that a complex query? What would make it not complex? Also you might want to consider using ANSI-92 style joins. — Conrad Frix, May 17 '12 at 19:41
@HLGEM do you mean that he should do the 6 joins instead of the above implicit join? (is that perhaps how the engine will execute it?) I'm actually asking. — keyser, May 17 '12 at 19:44
@Keyser see http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 — Conrad Frix, May 17 '12 at 19:45
@ConradFrix Was hoping for a more revolutionizing answer :p, but thanks for the perfect link! — keyser, May 17 '12 at 19:48

score 1 · Answer 1 · answered May 17 '12 at 19:39

1

It is actually a very simple query, just many tables.

Rather than trying to retrieve everything in one big query, which will result in much duplicated data in your output, I would do one query to retrieve user/board/picture info, another query for followers, and another query for comments.

Also, you can use ANSI syntax for better readability.

answered May 17 '12 at 19:39

D'Arcy Rittich

167,292
40
290
283

In this way, I need to do one query for each following and one query for comments of each picture. So, If in homepage will be showed 50 last pictures of my following, then I need to do 1 query to retrieve followings's pictures + 50 queries to retrieve comments of each pictures. – xRobot May 17 '12 at 20:01
no, you would do one query that retrieves comments for all 50 pictures – D'Arcy Rittich May 17 '12 at 20:56

vyegorov · Accepted Answer · 2012-07-09T17:49:38.867

Here're the DDL statements deduced from your query:

CREATE TABLE users (id integer, username varchar(30));
CREATE TABLE boards (id integer, title varchar(30));
CREATE TABLE pictures (id integer, link varchar(90));
CREATE TABLE boards_pictures (
  id_user integer,
  id_picture integer,
  boards_id integer);
CREATE TABLE followers (id_user integer, id_following integer);
CREATE TABLE comments (picture_id integer, comment varchar(350));

You're mixing column naming styles here, compare users.id, followers.id_user, comments.picture_id and board_pictures.id_picture (last 2 are quite misleading). The more tables you'll have, the more attention you'll have to pay on how column is named. It's better to stick to a single common pattern, picture_id or user_id being most appropriate in my view.

Your query is not complicated, except you're using implicit join notation. It is not the recommended way, as there's a chance to miss some predicates and end up with Cartesian product of 2 (or more) tables.

Your query can be rewritten like this:

SELECT u.username, p.link, c.comment, b.title
  FROM boards_pictures bp
  JOIN pictures p ON p.id = bp.id_picture
  JOIN followers f ON bp.id_user = f.id_following
  JOIN users u ON u.id = f.id_following
  JOIN comments c ON c.picture_id = p.id
  JOIN boards b ON b.id = bp.boards_id
WHERE f.id_user = $_session['userid'];

As you see now, query is really simple and has only one predicate. I've created a testbed without data on SQL Fiddle.

In my view your structure is quite good. There's no need to change neither table design nor this query.

How to avoid this complex query?

2 Answers2