EverSQL Database Performance Knowledge Base

Question 1

SQL: Delete by primary key vs by foreign key performance?

Delete by primary key:

DELETE FROM dbo.Users WHERE Id IN (1,2,3,4,5)

Delete by some foreign key:

DELETE FROM dbo.Users WHERE Email_Id IN (454,851,909)

Suppose we're talking about ~30 million rows dbo.Users table, and IN() clause contains about 5-200K items.

Answer

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

ALTER TABLE `Users` ADD INDEX `users_idx_id` (`Id`);

SELECT
        1 
    FROM
        dbo.Users 
    WHERE
        dbo.Users.Id IN (
            1, 2, 3, 4, 5
        )

Question 2