MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Speed Up Queries / Question No: 28

How to speed up multi table based delete query?

This query works but is there any way of making it more elegant or speeding it up?
DELETE from bm_KW USING bm_KW, bmjn_KW WHERE bm_KW.KeywordID 
IN(SELECT bmjn_KW.KeywordID FROM bmjn_KW
WHERE bmjn_KW.ItemID = '1016' ) AND bm_KW.KeywordID
NOT IN (SELECT bmjn_KW.KeywordID FROM bmjn_KW
WHERE bmjn_KW.ItemID != '1016');

Its purpose is to delete only the keywords which are unique to the item being deleted, "1016" in this case.

Answer No: 28

Try the below, may it seems more speedy.
DELETE bm_KW FROM bm_KW INNER JOIN 
(SELECT kw2.KeywordID FROM bmjn_KW kw1
INNER JOIN bmjn_KW kw2 ON kw1.KeywordID = kw2.KeywordID
AND kw2.ItemID != '1016' WHERE kw1.ItemID = '1016'
) AS keywords
ON bm_KW.KeywordID = keywords.KeywordID;

Related MySQL FAQs to the Above FAQ

What-is-better-query-to-update-a-table What is better query to update a table?

How-to-get-all-dates-between-two-dates How to get all dates between two dates?

Why-my-update-query-works-slow-in-MySQL Why my update query works slow in MySQL?

What-is-query-cache-in-MySQL What is query cache in MySQL?

What-is-table-cache-in-MySQL What is table cache in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.