MySQL – Joining WordPress Comments with Post Titles

join;MySQL

I'm working with a wordpress database and need to get some data on the comments.

The goal is to get comments from a decommissioned site. I have the database running on my local system and can generate a report from the wp_comments table easy enough. That table has a column named 'comment_post_id' which is a direct correlation to the ID column in the wp_posts table.

I've dumped both from mysql into excel and done a vlookup from the comments table into the posts table to get the desired report.

While the dump->excel process works, I know this could probably be done with a query in mysql.

My first crack at it fails:

SELECT * FROM 'wp_posts' WHERE ('wp_comments.comment_post_id' = 'wp_posts.id');

and doesn't even include a join yet.

Can you provide any pointers or tips on getting a correct query?

Thank you.

Best Answer

SELECT * FROM wp_posts WHERE id IN (SELECT comment_post_id FROM wp_comments);

SELECT A.* FROM wp_posts A INNER JOIN
(SELECT comment_post_id FROM wp_comments) B
ON A.id = B.comment_post_id;

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 – Retrieving 10 records from multiple tables

With a slight waste of fetched rows, you can:

SELECT * FROM (
  SELECT * FROM table2 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 3
  UNION ALL
  SELECT * FROM table1 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 10
) select_both
LIMIT 10
;

At the worst case we will be reading 13 rows, then filtering them down to 10.

Best Answer

Related Solutions

Mysql – Help optimizing MySQL slow query

Mysql – Retrieving 10 records from multiple tables

Related Question