Mysql – INNER JOIN between 2 tables vs WHERE on one table performance

MySQLperformance

Suppose I have two tables:

CREATE TABLE `doctors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `clinic_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `clinics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

The next queries returns the same data but for large tables wich is better for performance?

  1. Query with INNER JOIN

    SELECT doctors.*
    FROM doctors
    INNER JOIN clinics
    ON clinics.id = doctors.clinic_id
    WHERE clinics.id = 1;

  2. Query only with WHERE

    SELECT doctors.*
    FROM doctors
    WHERE doctors.clinic_id = 1;

Best Answer

The second query. But why are you comparing them?

They may return the same data but they are answering two different questions. The first retrieves all records from doctors where a record exists in clinic with id=1. The second retrieves all records from doctors with clinic_id=1 regardless of whether a record exists in clinic.

In the absence of a foreign key constraint between the two tables, the queries are not comparable.

Related Question