Complex SQL (possibly external joins)

Question

I have 2 tables as below.

Review table
identifier
user_id
overview
title
datetime

PeopleFollows table
identifier
user_id
following_user_id
datetime

I want to run 1 query to get the top 10 results by date. So, for example, if I have the following data:

Review table

1 user1 "This is my review" "title" 2011-01-10 2 user1 "Another review" "title again" 2011-01-08 

People follow the table.

 1 user2 user1 2011-01-09 

I want to get the following results:

 Review id=2 People follows id=1 Review id = 1 

The only way I can do this is to make separate X-limit queries and then combine them to get the X results.

I may need to explain a little more.

Answer 1

You do not need full checks if you index both tables by date, and also order each query by time date before UNION.

 (SELECT "Review", id, datetime FROM Reviews ORDER BY datetime DESC LIMIT 10) UNION (SELECT "People", id, datetime FROM PeopleFollows ORDER BY datetime DESC LIMIT 10) ORDER BY datetime DESC LIMIT 10 

Answer 2

You can use UNION, but the query will not be optimal:

 (SELECT "Review", id FROM Reviews) UNION (SELECT "People", folowing_user_id FROM PeopleFollows) ORDER BY datetime DESC LIMIT 10 

You cannot use the correct indexes in this query, it will be fullscan. Therefore, use it at your own risk.

The best idea is not to try to join tables. To do this, use the third cache table.