mysql - 如何优化这个繁重的MySQL查询？

我需要优化一个MySQL查询，该查询需要花费很多时间才能加载。

这里是 ：

SELECT
p.id,
UNIX_TIMESTAMP(p.last_answer_date) AS last_answer_date_timestamp,
p.sender_id,
p.recipient_id,
p.is_read_sender,
p.last_answer_user_id,
p.is_read_recipient,
(SELECT m.read FROM pm_message m WHERE m.conv_id = p.id AND m.user_id != $user_id ORDER BY m.date DESC LIMIT 1) AS read_status,
(SELECT m.content FROM pm_message m WHERE m.conv_id = p.id ORDER BY m.date DESC LIMIT 1) AS last_message,
(SELECT u.username FROM user u WHERE (u.id = p.sender_id OR u.id = p.recipient_id) AND u.id != $user_id LIMIT 1) AS from_username,
(SELECT u.id FROM user u WHERE (u.id = p.sender_id OR u.id = p.recipient_id) AND u.id != $user_id LIMIT 1) AS from_userid,
(SELECT ui.gender FROM user_info ui WHERE (ui.user_id = p.sender_id OR ui.user_id = p.recipient_id) AND ui.user_id != $user_id LIMIT 1) AS from_gender,
(SELECT ph.thumb_url FROM photo ph, user_info ui WHERE ui.main_photo = ph.id AND (ph.user_id = p.sender_id OR ph.user_id = p.recipient_id) AND ph.user_id != $user_id LIMIT 1) AS from_thumb_url
FROM pm_conv p
WHERE p.sender_id = $user_id OR p.recipient_id = $user_id
ORDER BY p.last_answer_date DESC LIMIT 25;

该查询为我提供了我想要的结果，但它确实很慢...而且我认为嵌套选择是此查询如此慢的原因。

这是此查询的表结构：

CREATE TABLE IF NOT EXISTS `photo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) DEFAULT NULL,
  `thumb_url` varchar(255) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `status` int(11) NOT NULL,
  `votes` int(11) DEFAULT '0',
  `comments` int(11) DEFAULT '0',
  `views` int(11) DEFAULT '0',
  `text` text,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `pm_conv` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `sender_id` int(11) NOT NULL,
  `recipient_id` int(11) NOT NULL,
  `last_answer_date` datetime NOT NULL,
  `nb_messages` int(11) NOT NULL,
  `is_read_sender` int(11) NOT NULL,
  `is_read_recipient` int(11) NOT NULL DEFAULT '0',
  `last_answer_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `recipient_id` (`recipient_id`),
  KEY `sender_id` (`sender_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `pm_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `content` text NOT NULL,
  `user_id` int(11) NOT NULL,
  `conv_id` int(11) NOT NULL,
  `read` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `conv_id` (`conv_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `encrypt_id` varchar(255) DEFAULT NULL,
  `register_date` datetime DEFAULT NULL,
  `last_login_date` datetime DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `banned` int(11) DEFAULT NULL,
  `banned_reason` text,
  `first_step_form` int(11) DEFAULT '0',
  `status` int(11) DEFAULT NULL,
  `valid_snapchat` int(11) DEFAULT '0',
  `introduced_forum` int(11) DEFAULT '0',
  `referer` varchar(255) DEFAULT NULL,
  `allow_social_featuring` int(11) DEFAULT NULL,
  `rank` int(11) DEFAULT NULL,
  `fb_id` bigint(20) DEFAULT NULL,
  `rate_app_status` int(11) DEFAULT NULL,
  `last_activity_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `user_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `gender` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `about` text,
  `main_photo` int(11) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `relation_type` varchar(30) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `fb_link` varchar(255) DEFAULT NULL,
  `twitter_link` varchar(255) DEFAULT NULL,
  `youtube_link` varchar(255) DEFAULT NULL,
  `instagram_link` varchar(255) DEFAULT NULL,
  `app_pref_forum` int(11) DEFAULT NULL,
  `app_pref_pm` int(11) DEFAULT NULL,
  `app_pref_snapchat_request` int(11) DEFAULT NULL,
  `browse_invisibly` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `main_photo` (`main_photo`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

有人可以帮我优化这个繁重的查询吗？

谢谢！

最佳答案

您可以在说明计划中看到无效索引正在访问某些表。尝试计算所有表的统计信息，以查看其是否发生了变化（使用分析表）。