Struggling to create an efficient index for this MYSQL query with JOIN and grou

Question

I'm running a MySQL query on a dataset that currently has approx 26M rows and is growing by 5-10M rows per year.

This is the primary table:

CREATE TABLE `ledger_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `patient_id` int(11) DEFAULT NULL,
  `provider_id` int(11) DEFAULT NULL,
  `location_id` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `ledger_entry_type_id` int(11) DEFAULT NULL,
  `local_pms_id` int(11) DEFAULT NULL,
  `external_id` varchar(255) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `amount_cents` int(11) DEFAULT NULL,
  `amount_currency` varchar(255) DEFAULT NULL,
  `remaining_amount_cents` int(11) DEFAULT NULL,
  `remaining_amount_currency` varchar(255) DEFAULT NULL,
  `balance_cents` int(11) DEFAULT NULL,
  `check_number` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `external_ledger_entry_type_id` varchar(255) DEFAULT NULL,
  `external_provider_id` varchar(255) DEFAULT NULL,
  `external_patient_id` varchar(255) DEFAULT NULL,
  `external_account_id` varchar(255) DEFAULT NULL,
  `external_guarantor_id` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `external_claim_id` varchar(255) DEFAULT NULL,
  `balance_currency` varchar(255) DEFAULT 'USD',
  `surface_mesial` tinyint(1) DEFAULT NULL,
  `surface_distal` tinyint(1) DEFAULT NULL,
  `surface_occlusal` tinyint(1) DEFAULT NULL,
  `surface_lingual` tinyint(1) DEFAULT NULL,
  `surface_facial` tinyint(1) DEFAULT NULL,
  `surface_class_5` tinyint(1) DEFAULT NULL,
  `tooth_range_start` int(11) DEFAULT NULL,
  `tooth_range_end` int(11) DEFAULT NULL,
  `primary_ins_paid_amount_cents` int(11) DEFAULT NULL,
  `secondary_ins_paid_amount_cents` int(11) DEFAULT NULL,
  `ignore_for_payroll` tinyint(1) DEFAULT '0',
  `external_updated_at` timestamp NULL DEFAULT NULL,
  `ar_remaining_amount_cents` int(11) DEFAULT NULL,
  `ar_remaining_amount_currency` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_ledger_entries_on_type_and_id` (`type`,`id`),
  UNIQUE KEY `index_ledger_entries_on_local_pms_id_and_external_id` (`local_pms_id`,`external_id`),
  KEY `index_ledger_entries_on_patient_id` (`patient_id`),
  KEY `index_ledger_entries_on_provider_id` (`provider_id`),
  KEY `index_ledger_entries_on_location_id` (`location_id`),
  KEY `index_ledger_entries_on_account_id` (`account_id`),
  KEY `index_ledger_entries_on_ledger_entry_type_id` (`ledger_entry_type_id`),
  KEY `index_ledger_entries_on_local_pms_id` (`local_pms_id`),
  KEY `index_ledger_entries_on_check_number` (`check_number`),
  KEY `date_loc_prov_type` (`date`,`location_id`,`provider_id`,`type`),
  KEY `index_le_on_external_let_id` (`external_ledger_entry_type_id`),
  KEY `index_le_on_external_provider_id` (`external_provider_id`),
  KEY `index_ledger_entries_on_type` (`type`),
  KEY `index_ledger_entries_on_date_and_location_id` (`date`,`location_id`),
  KEY `index_ledger_entries_on_date` (`date`),
  KEY `index_ledger_entries_on_type_and_location_id_and_date` (`type`,`location_id`,`date`),
  KEY `temp_location_id_let_id_date` (`location_id`,`ledger_entry_type_id`,`date`),
  KEY `index_le_on_location_let_date` (`location_id`,`ledger_entry_type_id`,`date`),
  KEY `index_le_on_external_account_id` (`external_account_id`),
  KEY `index_ledger_entries_on_external_id` (`external_id`),
  KEY `index_ledger_entries_on_date_and_type` (`date`,`type`),
  KEY `index_ledger_entries_on_provider_id_and_date_and_type` (`provider_id`,`date`,`type`),
  KEY `index_le_date_location_type_amount_cents` (`date`,`location_id`,`type`,`amount_cents`) USING BTREE,
  KEY `le_date_loc_type_prov_cent` (`date`,`location_id`,`type`,`provider_id`,`amount_cents`),
  KEY `index_ledger_entries_on_ledger_entry_type_id_and_date` (`ledger_entry_type_id`,`date`),
  KEY `le_date_type_loc_pro_cen_pat` (`date`,`type`,`location_id`,`provider_id`,`amount_cents`,`patient_id`),
  KEY `le_external_ids` (`id`,`external_id`,`local_pms_id`),
  KEY `le_balance_by_acct` (`balance_cents`,`account_id`),
  KEY `index_ledger_entries_on_account_id_and_amount_cents` (`account_id`,`amount_cents`),
  CONSTRAINT `fk_rails_29eb3a7e59` FOREIGN KEY (`ledger_entry_type_id`) REFERENCES `ledger_entry_types` (`id`),
  CONSTRAINT `fk_rails_95dd992850` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  CONSTRAINT `fk_rails_95dd992851` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=55586430 DEFAULT CHARSET=utf8;

And the query I need to optimize is:

SELECT ledger_entries.date, locations.acct_codename, ledger_entries.provider_id, providers.name, ledger_entry_types.shortcode, ledger_entry_types.title, COUNT(*), COUNT(DISTINCT ledger_entries.patient_id) as 'patient count', SUM(amount_cents)/100 as 'Total Amount' FROM ledger_entries
LEFT JOIN locations ON ledger_entries.location_id = locations.id
LEFT JOIN providers ON ledger_entries.provider_id = providers.id
LEFT JOIN ledger_entry_types ON ledger_entries.ledger_entry_type_id = ledger_entry_types.id
WHERE (ledger_entries.type IS NULL OR ledger_entries.type IN ('Procedure', 'Adjustment', 'AncillarySale'))
AND ledger_entries.date BETWEEN '2022-01-01' AND '2022-06-30'
AND locations.active = true
GROUP BY ledger_entries.date, ledger_entries.provider_id, ledger_entries.location_id, ledger_entries.ledger_entry_type_id;

And here is the EXPLAIN:

[
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "ledger_entries",
    "partitions": null,
    "type": "ALL",
    "possible_keys": "index_ledger_entries_on_type_and_id,date_loc_prov_type,index_ledger_entries_on_type,index_ledger_entries_on_date_and_location_id,index_ledger_entries_on_date,index_ledger_entries_on_type_and_location_id_and_date,index_ledger_entries_on_date_and_type,index_le_date_location_type_amount_cents,le_date_loc_type_prov_cent,le_date_type_loc_pro_cen_pat",
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 26699730,
    "filtered": 95.92,
    "Extra": "Using where; Using filesort"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "locations",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "4",
    "ref": "e_production.ledger_entries.location_id",
    "rows": 1,
    "filtered": 100.00,
    "Extra": "Using where"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "providers",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "4",
    "ref": "e_production.ledger_entries.provider_id",
    "rows": 1,
    "filtered": 100.00,
    "Extra": null
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "ledger_entry_types",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "4",
    "ref": "e_production.ledger_entries.ledger_entry_type_id",
    "rows": 1,
    "filtered": 100.00,
    "Extra": null
  }
]

This runs for a six month window in about 65 seconds. I am trying to get it under our timeout threshold for a related service of 30 seconds.

Answer 1

• I assume date BETWEEN '2022-01-01' AND '2022-06-30' includes more than about 20% of ledger_entries`?
• Drop INDEX(a), if you also have INDEX(a,b).
• Since you have a specific test locations.active = true, LEFT JOIN locations is actually INNER JOIN locations.
• Please qualify all column names (to make it easier to interpret which tables are referenced).

None of the above will speed up the query. Here's something that will:

Use UNION instead of OR:

SELECT ....  (( replacing ledger_entries with le3 ))
    FROM (
        ( SELECT le.id
            FROM ledger_entries AS le
            WHERE  le.type IS NULL
              AND  le.date >= '2022-01-01'
              AND  le.date  < '2022-01-01' + INTERVAL 6 MONTH
        ) UNION ALL (
          SELECT le.id
            FROM ledger_entries AS le
            WHERE ledger_entries.type IN
                    ('Procedure', 'Adjustment', 'AncillarySale')
              AND  le.date >= '2022-01-01'
              AND  le.date  < '2022-01-01' + INTERVAL 6 MONTH
        )
         ) AS le2
    JOIN ledger_entries AS le3 ON le3.id = le2.id
    JOIN locations  ON le3.location_id = locations.id
    LEFT JOIN ... (etc)
    WHERE locations.active
    GROUP BY  le3.date, le3.provider_id, le3.location_id, 
              le3.ledger_entry_type_id;

and replace index_ledger_entries_on_type with

INDEX(type, date)

Even faster... If it is practical to summarize the data daily (or monthly), then build and maintain a Summary Table and do the query against it.