MySQL query optimization improvement

Question

Hello all I have a MySQL query that fetches data from more than on tables. Here is my query

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

It gets data from user table, service table, review table and providerphoto table. It works too but the execution time is very slow. I guess to make it a single query and avoid the inner five queries may run it fast. Any help?

Table structures.

--
-- 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

Something like this. This will return all the values for the users as I did not use the ORDER and LIMIT in the query. This is just for the approach.

SELECT 
            a.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,
            MIN(s.service_price) as price,
            s.service_recomanded as recomandad,
            verified_email,
            verified_facebook,
            verified_phone,
            verified_twitter,
            pp.providerphoto_name as photo,
            ROUND( AVG(r.review_rate),2) as rate,
            s.service_ICOC as type
         FROM 
            user a LEFT JOIN service s on s.user_id = a.user_id LEFT JOIN providerphoto pp on pp.user_id = a.user_id LEFT JOIN review r on r.user_id = a.user_id 
        WHERE a.user_type = 'provider' 
            AND a.user_active=1 
            AND a.user_deleted=0;

Answer 2

First thing I would do is index user.user_type, user.user_deleted and user.user_active to let the optimizer quickly start with a smaller set of user records to have to deal with.

Do you mean to allow service.user_id to be NULL? That seems like a mistake.

Also you may want to throw indexes on any columns where you're doing an ORDER BY.

The best way to find out what is slow in this query is to do a EXPLAIN QUERY and analyze what it tells you. We can help you with that as well.

Answer 3

You can probably just add indexes to speed up the query. Try adding the following indexes:

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