Are these two queries identical?

Tags: mysql sql
Question!

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?

By : Martin AJ


Answers

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.



You have to get the collection object first as mentioned below

  var collection = db.collection("simple_document_insert_collection_no_safe");

// Insert a single document collection.insert({hello:'world_no_safe'});

Please refer the below link for more details.

mongod db collection using nodejs



I think you could rewrite this as a single query on the status table, using ORs to test each scenario, like so:

SELECT ss.int_tran_id
FROM   status SS
WHERE  ss.stage in ('ACHPayment_Confirmed', 'HIFV4', 'HIFV5_FTRINF', 'Payment_HIFV5_FTRINF')
OR     (ss.stage = 'PREVDAY'
        AND (EXISTS (SELECT NULL
                     FROM   references rf
                     WHERE  ss.int_tran_id = rf.int_tran_id
                     AND    rf.mid_ref IS NOT NULL)
             OR EXISTS (SELECT NULL
                        FROM   app_data ad
                               INNER JOIN ach aa
                                 ON (ad.app_data_id = aa.ach_id)
                        WHERE  ss.int_tran_id = ad.int_tran_id
                        AND    aa.par_number IS NOT NULL)));
By : Boneist


This video can help you solving your question :)
By: admin