MySQL查询优化改进

Question

大家好，我有一个 MySQL 查询，它可以从多个表中获取数据。这是我的查询

SELECT 
                user_id as id,
                user_name as name,
                user_phone as phone,
                user_email as email,
                user_address1 as address1,
                user_address2 as address2,
                user_city as city,
                user_state as state,
                user_country as country,
                user_available as available,
                user_location as location,
                user_latitude as latitude,
                user_longitude as longitude,
                user_description as description,
                user_company as company,
                user_gender as gender,
                (SELECT MIN(service_price) FROM service WHERE service.user_id = a.user_id) as price,
                (SELECT service_recomanded FROM service WHERE service.user_id = a.user_id ORDER BY service.service_price ASC LIMIT 1) as recomandad,
                verified_email,
                verified_facebook,
                verified_phone,
                verified_twitter,
                (SELECT providerphoto_name FROM providerphoto WHERE providerphoto.user_id = a.user_id ORDER BY providerphoto_order ASC LIMIT 1 ) as photo,
                (SELECT ROUND( AVG(review_rate),2) FROM review WHERE review.user_id = a.user_id ) AS rate,
                (SELECT service_ICOC FROM service WHERE service.user_id = a.user_id ORDER BY service_price ASC LIMIT 1) as type
             FROM 
                user a
            WHERE a.user_type = 'provider' 
                AND a.user_active=1 
                AND a.user_deleted=0

它从用户表、服务表、评论表和提供商照片表中获取数据。它也可以工作，但执行时间非常慢。我想将其设为单个查询并避免内部五个查询可能会运行得更快。有什么帮助吗？

表结构。

--
-- Table structure for table `providerphoto`
--

CREATE TABLE IF NOT EXISTS `providerphoto` (
  `providerphoto_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `providerphoto_file` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `providerphoto_name` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `providerphoto_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `providerphoto_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`providerphoto_id`),
  KEY `user_id` (`user_id`),
  KEY `providerphoto` (`user_id`,`providerphoto_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=487 ;

-- --------------------------------------------------------

--
-- Table structure for table `review`
--

CREATE TABLE IF NOT EXISTS `review` (
  `review_id` int(11) NOT NULL AUTO_INCREMENT,
  `review_title` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` int(11) NOT NULL,
  `review_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `review_content` text COLLATE utf8_unicode_ci NOT NULL,
  `review_user_id` int(11) NOT NULL,
  `review_rate` int(10) NOT NULL DEFAULT '1',
  `review_tip` int(11) NOT NULL DEFAULT '0',
  `service_booked` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`review_id`),
  KEY `user_id` (`user_id`),
  KEY `review_date` (`review_date`),
  KEY `review_user_id` (`review_user_id`),
  KEY `review_rate` (`review_rate`),
  KEY `review_tip` (`review_tip`),
  KEY `service_booked` (`service_booked`),
  KEY `review` (`user_id`,`review_rate`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97 ;

-- --------------------------------------------------------

--
-- Table structure for table `service`
--

CREATE TABLE IF NOT EXISTS `service` (
  `service_id` int(11) NOT NULL AUTO_INCREMENT,
  `service_name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `service_created_by` int(11) NOT NULL DEFAULT '0',
  `service_ICOC` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `service_price` int(11) NOT NULL,
  `service_date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `service_date_expire` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `service_time` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `service_rate` varchar(256) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `service_type` int(10) NOT NULL DEFAULT '1' COMMENT '1-in call, 2-out call, 3-in&out call',
  `service_recomanded` int(2) NOT NULL DEFAULT '0',
  `service_genre` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `service_message` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`service_id`),
  KEY `user_id` (`user_id`),
  KEY `service_ICOC` (`service_ICOC`(255)),
  KEY `service` (`user_id`,`service_price`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=854 ;

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_password` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `user_email` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_phone` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_address1` text COLLATE utf8_unicode_ci,
  `user_address2` text COLLATE utf8_unicode_ci NOT NULL,
  `user_city` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `user_state` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `user_country` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `user_company` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `user_birthday` date DEFAULT NULL,
  `user_register_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `user_type` enum('provider','client') COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_description` text COLLATE utf8_unicode_ci NOT NULL,
  `user_available` int(10) NOT NULL DEFAULT '1',
  `verified_email` tinyint(1) NOT NULL DEFAULT '0',
  `verified_facebook` tinyint(1) NOT NULL DEFAULT '0',
  `verified_phone` tinyint(1) NOT NULL DEFAULT '0',
  `verified_twitter` tinyint(1) NOT NULL DEFAULT '0',
  `user_facebook_friends` int(11) NOT NULL DEFAULT '0',
  `user_twitter_friends` int(11) NOT NULL DEFAULT '0',
  `user_longitude` decimal(10,5) NOT NULL DEFAULT '0.00000',
  `user_latitude` decimal(10,5) NOT NULL DEFAULT '0.00000',
  `user_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `user_gender` int(11) NOT NULL DEFAULT '0',
  `user_facebook_token` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `user_active` tinyint(4) NOT NULL DEFAULT '1',
  `user_location` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `user_push_notification_token` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `user_timezone_diff` int(11) NOT NULL DEFAULT '0',
  `balanced_uri` text COLLATE utf8_unicode_ci NOT NULL,
  `user_reset_passwd_token` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `is_test_user` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`),
  KEY `deleted_idx` (`user_deleted`),
  KEY `email_idx` (`user_email`(255))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=426 ;

Answer 1

您可能只需添加索引即可加快查询速度。尝试添加以下索引:

service(user_id, service_price)
providerphoto(user_id, providerphoto_order)
review(user_id, review_rate)