Mysql – the strategy of query evaluation in DBMS when Limit is used in query

database-designdatabase-recommendationMySQL

I have following tables in database

post(post_id , user_id , post_text , parsed_text) ;
Comment(comment_id , post_id , user_id , comment_text) ;
friend(id , user_id , friend_id ) ;
group (id , user_id ,group_id ) ;
like  (id, user_id , page_id ) ;

SQL to retrieve data from posts

 $posts =  mysql_query("select * from post where user_id in 
(select f_id  from ((select friend_id as f_id from friend  where user_id = 'a') 
union (select group_id as f_id  from group  where user_id = 'a') 
union (select page_id as f_id   where user_id = 'a'  ))) order by post_id limit 0 , 30");

In these table there are BTREE index on post_id in post table , comment_id in comment table and id in like , friend , share table ?

How DBMS evaluates this query to get 30 rows .

Is there any way to retrieve data from database from bottom of the table ? Bottom in the sense id is auto increment so i need to retrieve data from database as

If table has id 'N' then it should start retrieving data as 
first 'n-1' th'tupple ,second  'n-2' th tupple ,third 'n-3' th tupple and so on 
without using 'ORDER BY' .

This can reduce time complexity for above query .

Best Answer

Simplifying down to the post table, for discussion.

Create a unique index on the post_id.

CREATE UNIQUE INDEX recent_post_id ON post(post_id)

Then query the data as such:

SELECT * 
FROM post 
WHERE user_id IN (your criteria) 
ORDER BY post_id DESC
LIMIT 30

Of course, the most overhead may well be your nested collection of rows that you UNION together to get the criteria for recent posts.

Related Solutions

Mysql – Several values from different tables

Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:

INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`) 
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);

After you have done that your query should be more like the result you are after. Running this query:

select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;

returns the following result set:

user_id username    friend_id   friend  status_id   message created_at  privacy user_id is_reply    device  
1   admin   2   demo    1   dasdasdasdasdasd    2012-08-13 19:45:37 NULL    1   0   
2   demo    1   admin   2   dasdasdasdasdasd    2012-08-13 19:46:03 NULL    2   0   
1   admin   2   demo    3   dasdsad344hbvnbnhjhgjhjghjhj    2012-08-13 21:54:53 NULL    1   0

Is this anywhere near what you are looking for?

MySql: #1221 – Incorrect usage of UNION and ORDER BY

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM     
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp 
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.* 
FROM comment AS tbl 
    JOIN 
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION 
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp 
      ON tmp.sscm_id = tbl.sscm_id ;

Best Answer

Related Solutions

Mysql – Several values from different tables

MySql: #1221 – Incorrect usage of UNION and ORDER BY

Related Question