Mysql – UNION or SELF JOIN

join;MySQLunion

I have a table to hold parent and child posts together.

    +------+----------------+--------------+-------------+------------+
    | p_id | parent_post_id | child_status | post_status | post_title |
    +------+----------------+--------------+-------------+------------+
    |    1 |              0 |            1 | publish     | New 1      |
    |    2 |              1 |            0 | publish     | ab 1       |
    |    3 |              1 |            0 | publish     | ab2        |
    |    4 |              0 |            0 | publish     | new2       |
    |    5 |              4 |            0 | publish     | ab3        |
    +------+----------------+--------------+-------------+------------+

I want to show all parents from this table along with the child posts if the child_status of the parent is set to true.

Currently I use a self join to accomplish the same

    SELECT p1.*
    FROM wp_bw_post p1
    LEFT OUTER JOIN wp_bw_post p2
    ON p1.parent_post_id=p2.p_id
    WHERE (p1.parent_post_id=0 OR p2.child_status=1) AND p1.post_status="publish";

which gives me the expected results.

    +------+----------------+--------------+-------------+------------+
    | p_id | parent_post_id | child_status | post_status | post_title |
    +------+----------------+--------------+-------------+------------+
    |    1 |              0 |            1 | publish     | New 1      |
    |    2 |              1 |            0 | publish     | ab 1       |
    |    3 |              1 |            0 | publish     | ab2        |
    |    4 |              0 |            0 | publish     | new2       |
    +------+----------------+--------------+-------------+------------+
    4 rows in set (0.00 sec).

Performance is my great concern as this data is presented using infinite scroll.The table is expected to have millions of records and I need to present it as a single queue using some where and orderby conditions.

Is this the efficient way to do this, or should I store the parent and child posts in a different table and fetch all using UNION?

Best Answer

I suspect joining like this is the better choice. Just make sure you have the right indexes, perhaps even on all fields from your where.

I would open up Mysql Workbench and do a graphical EXPLAIN on it, so you can see the query plan. You can then try the other situation too and compare the results. Look for inefficient things like tmp_table and file_sort (relevant when ordering), full table scans, etc.

When you have that infinite scroll BTW, I assume you won't look up millions of rows at once? You will obtain the next x whenever somebody scrolls down?

Related Question