Martin AJ Martin AJ - 2 months ago 14
MySQL Question

Are these two queries identical?

I'm seeing this query in my codes:

INSERT INTO comments (post_id, user_id, table_code, content, author_id, pinged_id, date_time)
SELECT ?,?,?,?,?,?,?
FROM qanda qa, users u
WHERE qa.id=? and author_id IS NULL
limit 1;


I've written query above already .. Now I believe
, user u
is redundant in my query. Am I right?

Do you know why I'm asking this question? Because always there is a reason in the behind of all my codes. Now I cannot remember what's the reason of writing this
, users u
. That's why I worry to remove it.

Anyway, removing
, users u
changes something? or it totally redundant?

Answer

Since author_id belongs to the qanda table, the implicit join to the users table serves no purpose. In fact, it is a cross join, since there are no join conditions, which means it could be a real performance hog. You can try using the following simplified INSERT statement:

INSERT INTO comments (post_id, user_id, table_code, content, author_id,
                      pinged_id, date_time)
SELECT ?,?,?,?,?,?,?
FROM qanda qa
WHERE qa.id=? AND author_id IS NULL
LIMIT 1

But this doesn't explain what the users table is doing there in the first place. Maybe you inherited this query from someone (or something) else, and at one time the join was being used for a legitimate reason. Regardless, remove the debt now while you can easily do so.

Comments