CODEWITHSUNDEEP
mysqlsqlperformancedatesubquery
Dmitriy R
Dmitriy R

Reputation: 11

DATE_ADD in a subquery slows down execution

Good day. Why date modification in a subquery reduces performance?

This request is fast (0.015 sec):

SELECT p.id, p.name, s.value
FROM points p
LEFT JOIN settings s ON p.id = s.point_id AND s.type_id = 1
WHERE p.parent_id = 1310
AND NOT EXISTS (SELECT 1 FROM events e WHERE e.point_id = p.id AND e.start_at > '2019-09-09 20:00:00')

UPD Explain:

'1', 'PRIMARY', 'p', 'ref', 'idx_parent_id_type', 'idx_parent_id_type', '5', 'const', '29', 'Using where'
'1', 'PRIMARY', 's', 'ref', 'p_id_idx,sid_idx', 'p_id_idx', '4', 'rm_api.p.id', '1', 'Using where'
'2', 'MATERIALIZED', 'e', 'range', 'idx_start_at_end_at,point', 'idx_start_at_end_at', '5', NULL, '3855', 'Using index condition'

This request is slow (~ 18 sec):

SELECT p.id, p.name, s.value
FROM points p
LEFT JOIN settings s ON p.id = s.point_id AND s.type_id = 1
WHERE p.parent_id = 1310
AND NOT EXISTS (SELECT 1 FROM events e WHERE e.point_id = p.id AND e.start_at > DATE_ADD('2019-09-09 20:00:00', INTERVAL COALESCE(s.value, 0) MINUTE))

UPD Explain:

1   PRIMARY p   ref idx_parent_id_type  idx_parent_id_type  5   const   29  
1   PRIMARY s   ref p_id_idx,sid_idx    p_id_idx    4   rm_api.p.id 1   Using where
2   DEPENDENT SUBQUERY  e   ref idx_start_at_end_at,point   point   4   rm_api.p.id 478 Using index condition; Using where

What can be done? I doubt that I am using s.value correctly in the subquery

p.s. this request is also fast:

SELECT p.id, p.name, s.value
FROM points p
LEFT JOIN settins s ON p.id = s.point_id AND s.type_id = 1
WHERE p.parent_id = 1310
AND NOT EXISTS (SELECT 1 FROM events e WHERE e.point_id = p.id AND e.start_at > DATE_ADD('2019-09-09 20:00:00', INTERVAL 15 MINUTE))

UPD

Table points:

CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(160) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Не указано',
  `parent_id` int(10) unsigned DEFAULT 1,
  `type` int(11) NOT NULL DEFAULT 0,
  `status` int(11) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT '1970-12-31 09:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '1970-12-31 09:00:00',
  `capacity` int(11) NOT NULL DEFAULT 0,
  `is_building` int(11) DEFAULT 0,
  `is_ews_linked` int(11) NOT NULL DEFAULT 0,
  `ews_resource_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `map_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `other` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `human_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `booking_max_duration` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_parent_id_type` (`parent_id`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=2958 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Table settings:

CREATE TABLE `points_settins_types_storage` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `point_id` int(11) unsigned NOT NULL,
  `settings_prop_type_id` int(11) unsigned NOT NULL,
  `value` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`id`,`settings_prop_type_id`,`point_id`),
  KEY `p_id_idx` (`point_id`),
  KEY `sid_idx` (`settings_prop_type_id`),
  CONSTRAINT `pid` FOREIGN KEY (`point_id`) REFERENCES `points` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `sid` FOREIGN KEY (`settings_prop_type_id`) REFERENCES `points_settings_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1062 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Table events:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_at` datetime NOT NULL,
  `end_at` datetime NOT NULL,
  `point_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT 0,
  `owner_id` int(11) NOT NULL,
  `approver_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `date` datetime NOT NULL,
  `approver_message` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_message` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `linked_vks_id` int(11) DEFAULT NULL,
  `ews_event_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_from_exchange` int(11) NOT NULL DEFAULT 0,
  `ews_event_change_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` varchar(10000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `owner_id_k` (`owner_id`),
  KEY `idx_start_at_end_at` (`start_at`,`end_at`),
  KEY `end_start` (`end_at`,`start_at`) USING BTREE,
  KEY `date` (`date`) USING BTREE,
  KEY `status` (`status`) USING BTREE,
  KEY `point` (`point_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2338492 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Upvotes: 1

Views: 121

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

For your query, start with in index on events(point_id, start_at). Perhaps that will help.

Probably not.

If the interval values are always positive, then try this redundant version (with the above index):

WHERE p.parent_id = 1310 AND
      NOT EXISTS (SELECT 1
                  FROM events e
                  WHERE e.point_id = p.id AND
                        e.start_at > '2019-09-09 20:00:00' AND
                        e.start_at > DATE_ADD('2019-09-09 20:00:00', INTERVAL COALESCE(s.value, 0) MINUTE)
                 )

Yes, this looks silly. But the additional condition should make it easier to MySQL to use the index correctly.

Upvotes: 1

Related Questions