我有一个基本的查询，它在小数据集上运行得很好，但是当您得到100000个结果时，查询将永远（1.9秒）运行。请帮助可能的索引，因为可能我错过了一些东西（已经索引相当广泛）。下面是查询

SELECT
    count( * ) AS counts 
FROM
    pages pg
    INNER JOIN products AS p
        ON p.page_id = pg.id
    INNER JOIN pages_description AS pgd
        ON pg.id = pgd.page_id
            and pgd.language_id = 1
WHERE
    pgd.active = 1 
    AND pgd.deleted = 0 
    AND pg.type = 4 
    AND pg.created_date between '2018-01-30 06:00:00' and '2018-12-30 09:00:00'
    AND pg.modified_date between '2018-01-30 07:00:00' and '2018-12-30 09:00:00'

CREATE TABLE `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent` int(11) DEFAULT NULL,
  `type` int(11) DEFAULT NULL,
  `template_id` int(11) DEFAULT NULL,
  `link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `local_left` tinyint(1) DEFAULT NULL,
  `sort_order` int(11) DEFAULT NULL,
  `faceted_search` tinyint(1) DEFAULT NULL,
  `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_parent` (`parent`) USING BTREE,
  KEY `idx_type` (`type`) USING BTREE,
  KEY `idx_template` (`template_id`) USING BTREE,
  KEY `idx_local_left` (`local_left`) USING BTREE,
  KEY `idx_sort` (`sort_order`) USING BTREE,
  KEY `idx_faceted_search` (`faceted_search`) USING BTREE,
  KEY `idx_dates` (`type`,`created_date`,`modified_date`,`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=149352 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `pages_description` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL DEFAULT '1',
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `menu_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` text COLLATE utf8_unicode_ci,
  `sub_content` text COLLATE utf8_unicode_ci,
  `seo_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `h1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `keywords` text COLLATE utf8_unicode_ci,
  `title_tag` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_description` varchar(390) COLLATE utf8_unicode_ci DEFAULT NULL,
  `canonical_tag` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `unq_page_lang` (`page_id`,`language_id`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_menu_name` (`menu_name`) USING BTREE,
  KEY `idx_active` (`active`) USING BTREE,
  KEY `idx_deleted` (`deleted`) USING BTREE,
  KEY `idx_lang` (`language_id`),
  KEY `idx_page` (`page_id`) USING BTREE,
  KEY `idx_active_deleted_id` (`active`,`deleted`,`page_id`,`language_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=149319 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_id` int(11) NOT NULL,
  `on_hand` int(11) NOT NULL DEFAULT '0',
  `buy` tinyint(1) NOT NULL DEFAULT '0',
  `rfq` tinyint(1) NOT NULL DEFAULT '1',
  `model` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `parent_type` int(11) NOT NULL DEFAULT '1',
  `image` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_alt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `base_price` decimal(10,2) DEFAULT NULL,
  `length` decimal(10,5) DEFAULT NULL,
  `width` decimal(10,5) DEFAULT NULL,
  `height` decimal(10,5) DEFAULT NULL,
  `weight` decimal(10,5) DEFAULT NULL,
  `tax_class` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ready_to_ship` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `unq_page_id` (`page_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=148391 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我知道有人可能会认为1.9秒是永远的，但这是一个网络应用程序，需要有响应。我已经为有问题的表添加了模式。