How to speed up query in MySQL over 2 tables

0
votes

I running a very simple query that takes more than 60 seconds. Is there a way to speed up the query?

SELECT
    COUNT(salary.salary_amount),
    COUNT(invoice.invoiced_date) 
FROM sample_staff.salary

INNER JOIN sample_staff.invoice ON 1=1
   AND salary.employee_id = invoice.employee_id;

Each individual query runs OK:

SELECT
    COUNT(salary.salary_amount) 
FROM sample_staff.salary
; -- 2,844,047 records in 2.25 seconds

SELECT
    COUNT(invoice.invoiced_date) 
FROM sample_staff.invoice
; -- 973,488 records in 1.156 seconds

The structures of the table are:

CREATE TABLE `salary` (
   id` int unsigned NOT NULL AUTO_INCREMENT,
   employee_id` int unsigned NOT NULL DEFAULT '0',
  `salary_amount` decimal(11,2) NOT NULL DEFAULT '0.00',
  `from_date` date DEFAULT NULL,
  `to_date` date DEFAULT NULL,
  `insert_dt` datetime NOT NULL,
  `insert_user_id` int NOT NULL DEFAULT '-1',
  `insert_process_code` varchar(255) DEFAULT NULL,
  `update_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_user_id` int NOT NULL DEFAULT '-1',
  `update_process_code` varchar(255) DEFAULT NULL,
  `deleted_flag` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ak_salary` (`employee_id`,`from_date`,`to_date`),
  KEY `idx_employee_id` (`employee_id`),
  KEY `idx_salary_amount` (`salary_amount`),
  CONSTRAINT `salary_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2844050 DEFAULT CHARSET=utf8mb3

CREATE TABLE `invoice` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int unsigned NOT NULL,
  `invoiced_date` date NOT NULL,
  `paid_flag` tinyint NOT NULL DEFAULT '0',
  `insert_dt` datetime NOT NULL,
  `insert_user_id` int NOT NULL DEFAULT '-1',
  `insert_process_code` varchar(255) DEFAULT NULL,
  `update_dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_user_id` int NOT NULL DEFAULT '-1',
  `update_process_code` varchar(255) DEFAULT NULL,
  `deleted_flag` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`invoiced_date`),
  KEY `idx_employee_id` (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=973489 DEFAULT CHARSET=utf8mb3
/*!50100 PARTITION BY RANGE (year(`invoiced_date`))
SUBPARTITION BY HASH (month(`invoiced_date`))
SUBPARTITIONS 12
(PARTITION p1984 VALUES LESS THAN (1985) ENGINE = InnoDB,
 PARTITION p1985 VALUES LESS THAN (1986) ENGINE = InnoDB,
 PARTITION p1986 VALUES LESS THAN (1987) ENGINE = InnoDB,
 PARTITION p1987 VALUES LESS THAN (1988) ENGINE = InnoDB,
 PARTITION p1988 VALUES LESS THAN (1989) ENGINE = InnoDB,
 PARTITION p1989 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1990 VALUES LESS THAN (1991) ENGINE = InnoDB,
 PARTITION p1991 VALUES LESS THAN (1992) ENGINE = InnoDB,
 PARTITION p1992 VALUES LESS THAN (1993) ENGINE = InnoDB,
 PARTITION p1993 VALUES LESS THAN (1994) ENGINE = InnoDB,
 PARTITION pOTHER VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
mysqlquery-optimization