Suppose I have two tables:

CREATE TABLE `doctors` (
  `clinic_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)

CREATE TABLE `clinics` (
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)

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;

  • You can always try this yourself by EXPLAIN. If the plan of the first version contains more rows than that of the second one, it will surely be slower. (Which is my tip anyway.) Anyway, why would you use the first version? – dezso Nov 7 '12 at 10:24

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.

  • If there is a foreign key, the 2 queries will be equivalent but MySQL's optimizer cannot use that. MariaDB's optimizer though has Table elimination which I think does exactly that. – ypercubeᵀᴹ Nov 7 '12 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.