CODEWITHSUNDEEP
mysql
Sprunkas
Sprunkas

Reputation: 433

How to improve mysql query performance?

I have ~4 millions records in rental_schedules table and about 1k premise and it takes up to 1.5 s to fetch data. It's good time or I somehow can optimize more? This is my query:

select
  count(*) as aggregate
from
  `premises`
where
  exists (
    select
      *
    from
      `rental_schedules`
    where
      `premises`.`id` = `rental_schedules`.`premises_id`
      and `date` >= '2021-07-01'
      and `public_enterprise` = 0
      and `rental_schedule_status` = 1
  )
  and exists (
    select
      id
    from
      rental_schedules
    where
      premises.id = rental_schedules.premises_id
      and public = 1
      and public_enterprise = 0
  )

And this is my premises table indexes:

  PRIMARY KEY (`id`),
  KEY `premises_institution_id_foreign` (`institution_id`),
  KEY `premises_responsible_user_id_foreign` (`responsible_user_id`),
  KEY `premises_cover_type_id_foreign` (`cover_type_id`),
  KEY `premises_premises_type_id_foreign` (`premises_type_id`),
  CONSTRAINT `premises_cover_type_id_foreign` FOREIGN KEY (`cover_type_id`) REFERENCES `cover_types` (`id`),
  CONSTRAINT `premises_institution_id_foreign` FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `premises_premises_type_id_foreign` FOREIGN KEY (`premises_type_id`) REFERENCES `premises_types` (`id`),
  CONSTRAINT `premises_responsible_user_id_foreign` FOREIGN KEY (`responsible_user_id`) REFERENCES `dashboard_users` (`id`)

And this is my rental schedules table indexes:

  PRIMARY KEY (`id`),
  KEY `rental_schedules_date_index` (`date`),
  KEY `rental_schedules_week_day_id_foreign` (`week_day_id`),
  KEY `rental_schedules_rental_schedule_status_foreign` (`rental_schedule_status`),
  KEY `rental_schedules_contract_id_foreign` (`contract_id`),
  KEY `rental_schedules_bill_id_foreign` (`bill_id`),
  KEY `rental_schedules_accordion_id_index` (`accordion_id`),
  KEY `premises_search` (`premises_id`,`rental_schedule_status`,`date`,`public_enterprise`),
  KEY `rental_schedules_search` (`premises_id`,`public`,`public_enterprise`,`rental_schedule_status`),
  KEY `rental_schedules_search_with_date` (`premises_id`,`public`,`public_enterprise`,`rental_schedule_status`,`date`),
  CONSTRAINT `rental_schedules_bill_id_foreign` FOREIGN KEY (`bill_id`) REFERENCES `bills` (`id`) ON DELETE SET NULL,
  CONSTRAINT `rental_schedules_contract_id_foreign` FOREIGN KEY (`contract_id`) REFERENCES `contracts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `rental_schedules_premises_id_foreign` FOREIGN KEY (`premises_id`) REFERENCES `premises` (`id`),
  CONSTRAINT `rental_schedules_rental_schedule_status_foreign` FOREIGN KEY (`rental_schedule_status`) REFERENCES `rental_schedule_statuses` (`id`),
  CONSTRAINT `rental_schedules_week_day_id_foreign` FOREIGN KEY (`week_day_id`) REFERENCES `week_days` (`id`)

Thanks for help in advance!

Upvotes: 0

Views: 83

Answers (2)

Rick James
Rick James

Reputation: 142528

SELECT COUNT(*) AS aggregate
    FROM premises AS p
    JOIN rental_schedules AS rs  ON p.`id` = rs.`premises_id`
    WHERE `date` >= '2021-07-01'
      AND `public_enterprise` = 0
      AND `rental_schedule_status` = 1
      AND public = 1;

This 'composite' and 'covering' index on rental_schedules would help:

INDEX(public_enterprise, rental_schedule_status, public, date, premises_id)

The order of the columns matters -- = first, then range >=, then finish up with anything else needed for "covering".

If all those ids are BIGINT, be aware that each takes 8 bytes, and is probably overkill in size.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

I might have overlooked it, But I don't think you need 2 different EXISTS operators here. So your query can be optimized to -

select count(*) as aggregate
  from `premises`
 where exists (select null
                 from `rental_schedules`
                where `premises`.`id` = `rental_schedules`.`premises_id`
                  and `date` >= '2021-07-01'
                  and `public_enterprise` = 0
                  and `rental_schedule_status` = 1
                  and public = 1);

Upvotes: 1

Related Questions