Mysql – Selecting the popular posts using three tables

MySQL

I have three tables:

posts – contains the posts by users,
post_reviews – contains the likes on the posts, and
post_share – contains the post id and user id of that which is shared.

I would like to select five popular posts using these three tables. The popular posts are those that have the highest no of reviews and shares. I am finding it really hard to construct a SQL query for this.

A slight help or guidance in this matter will be highly appreciated.

UPDATE:

The table structure is as follows:

table posts{

id - PK
uid - FK
post - text
}

table post_reviews{

id - PK
pid - FK (Post Id)
uid - FK
like_unlike - ENUM
}

table post_share{

id - PK
pid - FK (Post Id)
uid - FK
}

Best Answer

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...

Related Solutions

Mysql – Join three tables

I essentially performed a LEFT JOIN of the occupation_field table against both of your queries, joining by means of the occuDscr field. Here is your new query:

SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    USING (occuDscr)
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    USING (occuDscr)
;

If the USING clause is not clear to you, here is your without the USING clause

SELECT
    oc.occuDscr job,
    IFNULL(postInfo.PostCount,0) Posts,
    IFNULL(userInfo.UserCount,0) Users
FROM
    (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
        FROM job_post INNER JOIN occupation_field
        ON pstOccuId = OccuId GROUP BY occuDscr
    ) postInfo
    ON oc.occuDscr = postInfo.occuDscr
    LEFT JOIN
    (
        SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
        FROM users INNER JOIN occupation_field
        ON usrOccuId = occuId GROUP BY occuDscr
    ) userInfo
    ON oc.occuDscr = userInfo.occuDscr
;

Here is your sample data loaded:

mysql> drop database if exists giannosfor;
Query OK, 3 rows affected (0.13 sec)

mysql> create database giannosfor;
Query OK, 1 row affected (0.00 sec)

mysql> use giannosfor
Database changed
mysql> create table occupation_field
    -> (occuId int not null auto_increment,
    -> occuDscr varchar(40),
    -> primary key (occuId));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into occupation_field (occuDscr) values
    -> ('Software Engineer'),('Economics'),('Structural Engineer'),
    -> ('Legal Advisors'),('Plumbers'),('Social Advisors'),('Musicians');
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> create table users
    -> (usrId int not null,
    -> usrName varchar(20),
    -> usrOccuId int not null);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into users values
    -> (2,'goodys',6),(5,'realmadrid',7),(6,'petousis',6);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table job_post
    -> (pstId int not null,pstTitle varchar(60),
    -> pstOccuId int not null,
    -> primary key (pstId));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into job_post values
    -> ( 4,'Software Engineer Recruit',1),
    -> ( 5,'Web Developer Recruit',1),
    -> ( 6,'Saxophonist',7),
    -> ( 7,'Construction Company looking for plumber.',5),
    -> ( 8,'Economic Analyst',2),
    -> ( 9,'Legal Advisor',4),
    -> (10,'Economic Assistant',2);
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from occupation_field;
+--------+---------------------+
| occuId | occuDscr            |
+--------+---------------------+
|      1 | Software Engineer   |
|      2 | Economics           |
|      3 | Structural Engineer |
|      4 | Legal Advisors      |
|      5 | Plumbers            |
|      6 | Social Advisors     |
|      7 | Musicians           |
+--------+---------------------+
7 rows in set (0.00 sec)

mysql> select * from users;
+-------+------------+-----------+
| usrId | usrName    | usrOccuId |
+-------+------------+-----------+
|     2 | goodys     |         6 |
|     5 | realmadrid |         7 |
|     6 | petousis   |         6 |
+-------+------------+-----------+
3 rows in set (0.00 sec)

mysql> select * from job_post;
+-------+-------------------------------------------+-----------+
| pstId | pstTitle                                  | pstOccuId |
+-------+-------------------------------------------+-----------+
|     4 | Software Engineer Recruit                 |         1 |
|     5 | Web Developer Recruit                     |         1 |
|     6 | Saxophonist                               |         7 |
|     7 | Construction Company looking for plumber. |         5 |
|     8 | Economic Analyst                          |         2 |
|     9 | Legal Advisor                             |         4 |
|    10 | Economic Assistant                        |         2 |
+-------+-------------------------------------------+-----------+
7 rows in set (0.00 sec)

mysql>

Here is the execution of the first query

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.00 sec)

mysql>

Here is the execution of the second query

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     ON oc.occuDscr = postInfo.occuDscr
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     ON oc.occuDscr = userInfo.occuDscr
    -> ;
+---------------------+-------+-------+
| job                 | Posts | Users |
+---------------------+-------+-------+
| Economics           |     2 |     0 |
| Legal Advisors      |     1 |     0 |
| Musicians           |     1 |     1 |
| Plumbers            |     1 |     0 |
| Social Advisors     |     0 |     2 |
| Software Engineer   |     2 |     0 |
| Structural Engineer |     0 |     0 |
+---------------------+-------+-------+
7 rows in set (0.01 sec)

mysql>

Give it a Try !!!

UPDATE 2012-01-11 11:50 EDT

To remove Jobs with no Posts, change the first LEFT JOIN into INNER JOIN:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
5 rows in set (0.00 sec)

mysql>

To remove Jobs with no Users, change the second LEFT JOIN into INNER JOIN:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------------+-------+-------+
| job             | Posts | Users |
+-----------------+-------+-------+
| Musicians       |     1 |     1 |
| Social Advisors |     0 |     2 |
+-----------------+-------+-------+
2 rows in set (0.00 sec)

mysql>

To remove Jobs with no Users or no Posts, change the both LEFT JOINs into INNER JOINs:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     INNER JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> ;
+-----------+-------+-------+
| job       | Posts | Users |
+-----------+-------+-------+
| Musicians |     1 |     1 |
+-----------+-------+-------+
1 row in set (0.00 sec)

mysql>

UPDATE 2012-01-11 12:03 EDT

To remove Jobs with no Post or Users, add a WHERE clause to check if either cound is greater than zero:

mysql> SELECT
    ->     oc.occuDscr job,
    ->     IFNULL(postInfo.PostCount,0) Posts,
    ->     IFNULL(userInfo.UserCount,0) Users
    -> FROM
    ->     (SELECT * FROM occupation_field ORDER BY occuDscr) oc
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(pstOccuId) AS 'PostCount'
    ->         FROM job_post INNER JOIN occupation_field
    ->         ON pstOccuId = OccuId GROUP BY occuDscr
    ->     ) postInfo
    ->     USING (occuDscr)
    ->     LEFT JOIN
    ->     (
    ->         SELECT occuDscr,COUNT(usrOccuId) AS 'UserCount'
    ->         FROM users INNER JOIN occupation_field
    ->         ON usrOccuId = occuId GROUP BY occuDscr
    ->     ) userInfo
    ->     USING (occuDscr)
    -> WHERE
    ->     IFNULL(postInfo.PostCount,0)>0 OR
    ->     IFNULL(userInfo.UserCount,0)>0
    -> ;
+-------------------+-------+-------+
| job               | Posts | Users |
+-------------------+-------+-------+
| Economics         |     2 |     0 |
| Legal Advisors    |     1 |     0 |
| Musicians         |     1 |     1 |
| Plumbers          |     1 |     0 |
| Social Advisors   |     0 |     2 |
| Software Engineer |     2 |     0 |
+-------------------+-------+-------+
6 rows in set (0.00 sec)

mysql>

Mysql – Inserting into three different tables, using foreign keys — 500000x each

Read the entire CSV into a new MyISAM table with no indexes.
Write SQL statements to migrate that data en-masse to the other 4 tables.
Drop that table.

100-150 IOPs -- not bad. That's about all you can get with a consumer-quality disk.

So, the trick is to minimize the IOPs.

What engine are you using? (InnoDB has some tunables that can help/hurt.)
What indexes? (The more indexes, the more I/O. And the cache may not be set optimally.)