MySQL – How to Join Three Tables in a Query

MySQL

Can you help me with this query:
I have three tables

first, main table

page_id | title
1 aaa
2 bbb
3 ccc
4 ddd

revision tbl:

id | rev_page | rev_text_id
1 1 1 
2 2 2
3 3 3
4 4 4
5 3 5
6 3 6
7 4 7

text table:

old_id | old_text
1 some_text_blob
2 some_text_blob
3 some_text_blob
4 some_text_blob
5 some_text_blob
6 some_text_blob
7 some_text_blob

I used this query

SELECT c.old_id,c.old_text 
FROM text c 
INNER JOIN revision b ON c.old_id=b.rev_text_id 
INNER JOIN page a ON a.page_id=b.rev_page 
WHERE a.page_id=3

and I get

3 some_text_blob
5 some_text_blob
6 some_text_blob

I need only the last one,
6 some_text_blob.

Best Answer

Order resulting table in the reverse chronological order and limit result to single row:

SELECT c.old_id,c.old_text 
  FROM text     AS c 
  JOIN revision AS b ON c.old_id=b.rev_text_id 
  JOIN page     AS a ON a.page_id=b.rev_page 
 WHERE a.page_id=3
 ORDER BY b.rev_text_id DESC
 LIMIT 1
;

Related Solutions

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Mysql – Selecting the popular posts using three tables

5 Most Popular Posts By Reviews

SELECT A.uid,B.post_count reviews,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_reviews
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

5 Most Popular Posts By Shares

SELECT A.uid,B.post_count shares,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_shares
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

I hope this gives you a start...

Best Answer

Related Solutions

Mysql – Help optimizing MySQL slow query

Mysql – Selecting the popular posts using three tables

Related Question