这个查询能更快吗？

Question

我有以下查询：

SELECT SQL_CALC_FOUND_ROWS *
FROM (
SELECT *
FROM 
(  
    SELECT M.id, M.project_id, M.reply_toaddress as reply_toemailaddress, M.phone_no, M.subject,M.message, M.timestamp_received, M.done, M.postpone,  "mail" AS type, M.firstname, M.prefix, M.surname
    FROM messages M
    LEFT JOIN link_projects_mailboxes LPMB 
        ON M.mailbox_id = LPMB.mailbox_id
    WHERE M.main_message_id =0  
         AND LPMB.projects_id = 13  AND ( 0 OR  (M.done = 0 AND M.postpone = 0 ))  AND M.status = 0  
    GROUP BY M.id 
) M
UNION 
(
    SELECT C.id, C.project_id, C.reply_toemailaddress, C.phone_no, C.subject,C.message, C.timestamp_received, done, postpone, "call" AS type , C.firstname, C.prefix, C.surname
    FROM calls C
    WHERE 1 
         AND projects_id = 13  AND ( 0 OR  (C.done = 0 AND C.postpone = 0 ))  AND C.status = 0  
) 
) x ORDER BY  `timestamp_received`  asc  LIMIT 30

问题是，该查询运行在700.000行上，数据为19.2GB。查询运行约3分钟。

如果我解释了查询，就会收到以下结果：

​

​

你们有什么建议吗？

编辑:显示创建表：

CREATE TABLE `messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `mailbox_id` int(11) NOT NULL,
  `submessage_of` int(11) NOT NULL,
  `main_message_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL COMMENT 'Takes project from afasmssql_sync DB',
  `categorie_id` int(11) NOT NULL,
  `call_id` int(11) NOT NULL,
  `to` text NOT NULL,
  `cc` text NOT NULL,
  `bcc` text NOT NULL,
  `message_id` varchar(255) NOT NULL,
  `bytes` int(11) NOT NULL,
  `from` varchar(255) NOT NULL,
  `sender` varchar(255) NOT NULL,
  `reply_toaddress` varchar(255) NOT NULL,
  `reply_toemailaddress` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `order_id` int(10) NOT NULL DEFAULT '0',
  `order_location` varchar(255) NOT NULL,
  `firstname` varchar(200) NOT NULL,
  `prefix` varchar(50) NOT NULL,
  `surname` varchar(200) NOT NULL,
  `emailaddress` varchar(200) NOT NULL,
  `phone_no` varchar(255) NOT NULL,
  `zipcode` varchar(6) NOT NULL,
  `house_no` varchar(6) NOT NULL,
  `house_no_add` varchar(50) NOT NULL,
  `street` varchar(200) NOT NULL,
  `city` varchar(200) NOT NULL,
  `country` varchar(50) NOT NULL,
  `language` varchar(50) NOT NULL,
  `message` longtext NOT NULL,
  `message_plain` longtext NOT NULL,
  `message_stripped` longtext NOT NULL,
  `quality_status` tinyint(1) NOT NULL,
  `quality_by` int(11) NOT NULL,
  `quality_date` datetime NOT NULL,
  `done` tinyint(1) NOT NULL,
  `done_date` datetime NOT NULL,
  `done_by` int(11) NOT NULL,
  `postpone` tinyint(1) NOT NULL,
  `status` int(11) NOT NULL,
  `manually` tinyint(1) NOT NULL,
  `timestamp_received` datetime NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `mailbox_id` (`mailbox_id`),
  KEY `submessage_of` (`submessage_of`),
  KEY `main_message_id` (`main_message_id`),
  KEY `subject` (`subject`),
  KEY `done` (`done`),
  KEY `postpone` (`postpone`),
  KEY `status` (`status`),
  KEY `project_id` (`project_id`),
  KEY `categorie_id` (`categorie_id`),
  KEY `call_id` (`call_id`),
  KEY `done_date` (`done_date`),
  KEY `timestamp_received` (`timestamp_received`),
  KEY `from` (`from`),
  KEY `reply_toemailaddress` (`reply_toemailaddress`),
  KEY `timestamp` (`timestamp`),
  FULLTEXT KEY `message` (`message`)
) ENGINE=MyISAM AUTO_INCREMENT=685579 DEFAULT CHARSET=utf8

CREATE TABLE `link_projects_mailboxes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `projects_id` int(11) NOT NULL,
  `mailbox_id` int(11) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `projects_id` (`projects_id`,`mailbox_id`)
) ENGINE=MyISAM AUTO_INCREMENT=156 DEFAULT CHARSET=latin1

CREATE TABLE `calls` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `actionline_id` int(11) NOT NULL,
  `projects_id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL COMMENT 'Takes project from afasmssql_sync DB',
  `categorie_id` int(11) NOT NULL,
  `call_direction` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `order_id` int(10) NOT NULL DEFAULT '0',
  `order_location` varchar(255) NOT NULL,
  `firstname` varchar(200) NOT NULL,
  `prefix` varchar(50) NOT NULL,
  `surname` varchar(200) NOT NULL,
  `reply_toemailaddress` varchar(200) NOT NULL,
  `phone_no` varchar(255) NOT NULL,
  `zipcode` varchar(6) NOT NULL,
  `house_no` varchar(6) NOT NULL,
  `house_no_add` varchar(50) NOT NULL,
  `street` varchar(200) NOT NULL,
  `city` varchar(200) NOT NULL,
  `country` varchar(50) NOT NULL,
  `language` varchar(50) NOT NULL,
  `message` longtext NOT NULL,
  `done` tinyint(1) NOT NULL,
  `done_date` datetime NOT NULL,
  `done_by` int(11) NOT NULL,
  `postpone` tinyint(1) NOT NULL,
  `status` int(11) NOT NULL,
  `timestamp_received` datetime NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `mailbox_id` (`actionline_id`),
  KEY `subject` (`subject`),
  KEY `done` (`done`),
  KEY `postpone` (`postpone`),
  KEY `status` (`status`),
  KEY `project_id` (`project_id`),
  KEY `projects_id` (`projects_id`),
  FULLTEXT KEY `message` (`message`)
) ENGINE=MyISAM AUTO_INCREMENT=8941 DEFAULT CHARSET=utf8

编辑:基于草莓答案的解释：

​

​

Answer 1

因此，为了使事情更加可读性，让我们从这个查询开始，然后对它运行解释.

SELECT SQL_CALC_FOUND_ROWS *
  FROM
     ( SELECT M.id
            , M.project_id
            , M.reply_toaddress as reply_toemailaddress
            , M.phone_no
            , M.subject
            , M.message
            , M.timestamp_received
            , M.done
            , M.postpone
            , "mail" type
            , M.firstname
            , M.prefix
            , M.surname
         FROM messages M
         JOIN link_projects_mailboxes LPMB 
           ON LPMB.mailbox_id = M.mailbox_id 
        WHERE M.main_message_id = 0  
          AND LPMB.projects_id = 13  
          AND M.done = 0 
          AND M.postpone = 0 
          AND M.status = 0  
        UNION 
       SELECT C.id
            , C.project_id
            , C.reply_toemailaddress
            , C.phone_no
            , C.subject
            , C.message
            , C.timestamp_received
            , C.done
            , C.postpone
            , "call" type 
            , C.firstname
            , C.prefix
            , C.surname
         FROM calls C
        WHERE C.projects_id = 13  
          AND C.done = 0 
          AND C.postpone = 0 
          AND C.status = 0  
     ) x 
 ORDER 
    BY timestamp_received ASC
 LIMIT 30;

请解释同样的情况：

+------+--------------+------------+------+------------------+-------------+---------+---------------------+-------+----------------+
| id   | select_type  | table      | type | possible_keys    | key         | key_len | ref                 | rows  | Extra          |
+------+--------------+------------+------+------------------+-------------+---------+---------------------+-------+----------------+
|    1 | PRIMARY      | <derived2> | ALL  | (NULL)           | (NULL)      | (NULL)  | (NULL)              |  218  | Using filesort |
+------+--------------+------------+------+------------------+-------------+---------+---------------------+-------+----------------+
|    2 | DERIVED      | LPMB       | ref  | projects_id      | projects_id | 4       |                     |    1  | Using index    |
+------+--------------+------------+------+------------------+-------------+---------+---------------------+-------+----------------+
|    2 | DERIVED      | M          | ref  | mailbox_id,      | mailbox_id  | 4       | ccc.LPMB.mailbox_id | 7,735 | Using where    |
|      |              |            |      | main_message_id, |             |         |                     |       |                |
|      |              |            |      | done,            |             |         |                     |       |                |
|      |              |            |      | postpone,        |             |         |                     |       |                |
|      |              |            |      | status           |             |         |                     |       |                |
+------+--------------+------------+------+------------------+-------------+---------+---------------------+-------+----------------+
|    3 | UNION        | C          | ref  | done,            | done        | 1       |                     |     4 | Using where    |
|      |              |            |      | postpone,        |             |         |                     |       |                |
|      |              |            |      | status,          |             |         |                     |       |                |
|      |              |            |      | projects_id      |             |         |                     |       |                |
+------+--------------+------------+------+------------------+-------------+---------+---------------------+-------+----------------+
|(NULL)| UNION RESULT | <union2,3> | ALL  | (NULL)           | (NULL)      | (NULL)  | (NULL)              | (NULL)|                |
+------+--------------+------------+------+------------------+-------------+---------+---------------------+-------+----------------+

Answer 2

由于两个内部SELECTs没有公共行，所以将UNION更改为UNION ALL。这样就省去了一张加速递的通行证。运行它们中的每一个，看看哪个比较慢，然后我们就可以专注于它了。

这些“复合”索引可能会帮助它运行得更快：

M:  INDEX(mailbox_id, message_id, done, postpone, status) -- in any order
calls:  INDEX(projects_id, done, postpone, status) -- in any order 

如果您不需要SQL_CALC_FOUND_ROWS，这将更快：

( SELECT ... FROM M ... ORDER BY ... LIMIT 30 )
UNION ALL
( SELECT ... FROM M ... ORDER BY ... LIMIT 30 )
ORDER BY ... LIMIT 30;   -- yes, repeated again

它需要适当的索引，可能是上面建议的索引，最后添加了timestamp_received。实际上无用的JOIN LPMB应该被AND EXISTS ( SELECT ... FROM LPMB ... )取代。

UNION+LIMIT技巧变得更加复杂，但如果您正在使用OFFSET进行分页，则仍然有可能。

无关：

去掉单个标志上的索引；它们通常是无用的。

您应该从MyISAM迁移到InnoDB。FULLTEXT (略有不同)可以在以后的版本中使用。

@dacrovinunghi MySQL没有“位图”索引类型。

WHERE 1和0 OR来自动态构建WHERE子句，但没有花时间保持它的干净。我更喜欢构建一个由AND'd组成的子句数组，然后构建一个implode子句。或者，如果没有，一起避免WHERE。

从表中移除“已完成”(等)项可能更好。这将消除WHERE的这一部分，缩小表，使其更加紧凑和高效。