Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

share|improve this question
    
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
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
You are absolutely right, thanks. –  ricardohead Nov 7 '12 at 1:58
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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