[Solved] JOIN\'ing by 3 tables and retrieving field based on content of those tables

How to optimize this SQL query?

In case you have your own slow SQL query, you can optimize it automatically here.

For the query above, the following recommendations will be helpful as part of the SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. 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.
Optimal indexes for this query:
ALTER TABLE `company` ADD INDEX `company_idx_id` (`id`);
ALTER TABLE `houses` ADD INDEX `houses_idx_completed` (`completed`);
ALTER TABLE `landlord_houses` ADD INDEX `landlord_houses_idx_house_id` (`house_id`);
ALTER TABLE `tenant` ADD INDEX `tenant_idx_house_id` (`house_id`);
The optimized query:
        DISTINCT h.id AS id,
        h.address1 AS address1,
        h.town AS town,
        h.postcode AS postcode,
        h.valid_from AS valid_from,
        h.valid_to AS valid_to,
            WHEN c.name IS NOT NULL THEN c.name 
            OR ' (MS)' 
            WHEN h.letting_agent IS NOT NULL THEN h.letting_agent 
            WHEN t.id IS NOT NULL THEN t.letting_agent 
            ELSE 'Unknown (Not set yet)' END) AS agent 
houses h 
landlord_houses lh 
    ON lh.house_id = h.id 
company c 
    ON c.id = lh.company_id 
tenant t 
    ON t.house_id = h.id 
h.deleted IS FALSE 
AND h.archived IS FALSE 
AND h.sign_up_complete IS TRUE 
AND h.completed > NOW() - '14 days'::INTERVAL 

Related Articles

* original question posted on StackOverflow here.