mysql - 优化查询/子查询以获得更好的性能(慢)

Question

我有一个带有子查询的查询，大约需要 15 秒才能完成。即使我不加入这两个表，速度也相当慢，所以消耗时间的不是加入。 jos_payplans_subscription 表有 8200 行，大小为 3.3 MB。

某些用户同时拥有有效和过期的成员(member)资格。我想找到所有用户如何不活跃(状态 = 1603)并且没有其他活跃成员(member)资格。

查询有效 - 只是速度慢。

问题:我可以做得更好吗？

谢谢

SELECT c.id, c.name, c.username, c.email, a.plan_id, d.title, a.subscription_date, a.expiration_date
FROM jos_payplans_subscription a
    LEFT JOIN jos_users c ON c.id = a.user_id
    LEFT JOIN jos_payplans_plan d ON d.plan_id = a.plan_id
WHERE a.status = 1603 
AND a.user_id NOT IN (SELECT b.user_id FROM jos_payplans_subscription b WHERE b.status = 1601)

这是 3 个表的创建表

CREATE TABLE `jos_payplans_subscription` (
  `subscription_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `total` decimal(15,5) DEFAULT '0.00000',
  `subscription_date` datetime DEFAULT '0000-00-00 00:00:00',
  `expiration_date` datetime DEFAULT '0000-00-00 00:00:00',
  `cancel_date` datetime DEFAULT '0000-00-00 00:00:00',
  `checked_out` int(11) DEFAULT '0',
  `checked_out_time` datetime DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime DEFAULT '0000-00-00 00:00:00',
  `params` text NOT NULL,
  PRIMARY KEY (`subscription_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=15103 DEFAULT CHARSET=utf8

CREATE TABLE `jos_payplans_plan` (
  `plan_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `published` tinyint(1) DEFAULT '1',
  `visible` tinyint(1) DEFAULT '1',
  `ordering` int(11) DEFAULT '0',
  `checked_out` int(11) DEFAULT '0',
  `checked_out_time` datetime DEFAULT '0000-00-00 00:00:00',
  `modified_date` datetime DEFAULT '0000-00-00 00:00:00',
  `description` text,
  `details` text,
  `params` text,
  PRIMARY KEY (`plan_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=utf8

CREATE TABLE `jos_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(400) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `username` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `sendEmail` tinyint(4) DEFAULT '0',
  `registerDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lastvisitDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `activation` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `params` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastResetTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Date of last password reset',
  `resetCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Count of password resets since lastResetTime',
  `otpKey` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Two factor authentication encrypted keys',
  `otep` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'One time emergency passwords',
  `requireReset` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Require user to reset password on next login',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_block` (`block`) USING BTREE,
  KEY `username` (`username`) USING BTREE,
  KEY `email` (`email`) USING BTREE,
  KEY `idx_name` (`name`(100)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=23158 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Answer 1

如果表中没有搜索字段，请不要忘记在搜索字段上添加索引。