EverSQL Database Performance Knowledge Base

Question 1

MySQL: Does more \"where\" conditions increase speed?

Database type:

I have a table:

ID | Name  | Status

With millions of entries. If I do a select:

SELECT * from table where ID = 949442

Will this query be in any way faster than this one:

SELECT * from table where ID = 949442 AND status = "OPEN"

Assuming that this row could have an OPEN or CLOSED status?

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.

Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
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 `table` ADD INDEX `table_idx_id` (`ID`);

SELECT
        * 
    FROM
        table 
    WHERE
        table.ID = 949442

Question 2