I've a query which will execute in cron job, it takes lots of time to execute, please help me to optimize this query:

SELECT up.id_user, up.fb_name, up.email, up.mobile, up.description, up.url_resume, up.date, up.fb_city, up.fb_country,
(SELECT su.status FROM hl_users_status_updates AS su WHERE su.id_user = up.id_user ORDER BY DATE DESC LIMIT 1) AS STATUS,
(SELECT COUNT(v.id_from) FROM hl_users_votes AS v WHERE v.id_from = up.id_user AND vote_status = 1) AS followers,
(SELECT COUNT(r.id_from) FROM hl_users_recommendations AS r WHERE r.id_from = up.id_user AND recommend_status = 1) AS recomends,
(SELECT COUNT(sp.id_social_profile) FROM hl_users_social_profiles AS sp WHERE sp.id_user = up.id_user) AS social_profiles,
(SELECT COUNT(ap.id_photo) FROM hl_users_album_photos AS ap WHERE ap.id_user = up.id_user) AS photos,
p.points, p.percentage
FROM hl_users_profiles AS up
INNER JOIN hl_users_points AS p ON p.id_user=up.id_user
ORDER BY p.percentage DESC


Thanks in advance

give us the EXPLAIN for the query, and do a SHOW CREATE TABLE for each table, which should reveal which indexes you have, and have not, declared

for the explain, please find the attachment,

the table structure as follows:

CREATE TABLE `hl_users_album_photos` (
`id_photo` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_user` bigint(10) unsigned NOT NULL,
`description` varchar(254) NOT NULL,
`date` int(10) unsigned NOT NULL,
PRIMARY KEY (`id_photo`),
KEY `id_user` (`id_user`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;



/*Table structure for table `hl_users_points` */



CREATE TABLE `hl_users_points` (
`id_point` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_user` bigint(20) unsigned NOT NULL,
`points` int(10) unsigned NOT NULL,
`percentage` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id_point`),
KEY `id_user` (`id_user`)
) ENGINE=MyISAM AUTO_INCREMENT=1096 DEFAULT CHARSET=latin1;



/*Table structure for table `hl_users_profiles` */



CREATE TABLE `hl_users_profiles` (
`id_user` bigint(20) unsigned NOT NULL,
`fb_name` varchar(100) CHARACTER SET utf8 NOT NULL,
`fb_fname` varchar(100) CHARACTER SET utf8 NOT NULL,
`fb_lname` varchar(100) CHARACTER SET utf8 NOT NULL,
`email` varchar(254) NOT NULL,
`mobile` bigint(20) unsigned NOT NULL,
`description` varchar(512) CHARACTER SET utf8 NOT NULL,
`url_resume` varchar(254) NOT NULL,
`profile_image_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 - fb image, 1 - uploaded image',
`url_share` varchar(100) NOT NULL,
`date` int(10) unsigned NOT NULL,
`fb_city` varchar(128) DEFAULT NULL,
`fb_country` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id_user`),
KEY `fb_city` (`fb_city`),
KEY `fb_country` (`fb_country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;



/*Table structure for table `hl_users_recommendations` */



CREATE TABLE `hl_users_recommendations` (
`id_recommend` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_from` bigint(20) unsigned NOT NULL,
`id_to` bigint(20) unsigned NOT NULL,
`src` tinyint(4) NOT NULL COMMENT '1 - invitation, 2 - wall post, 3 - mail, 4 - other links, 5 - profile visit, 6 - twitter',
`recommend_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 - not recommended, 1 - recommended, 2 - denied',
`testimonial` varchar(200) NOT NULL,
`date` int(10) unsigned NOT NULL,
PRIMARY KEY (`id_recommend`),
UNIQUE KEY `id_from` (`id_from`,`id_to`),
KEY `id_to` (`id_to`),
KEY `id_from index` (`id_from`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;



/*Table structure for table `hl_users_social_profiles` */



CREATE TABLE `hl_users_social_profiles` (
`id_social_profile` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_user` bigint(20) unsigned NOT NULL,
`social_profile_type` tinyint(3) unsigned NOT NULL,
`social_profile_url` varchar(254) NOT NULL,
`date` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id_social_profile`),
KEY `id_user` (`id_user`),
KEY `social_profile_type` (`social_profile_type`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;



/*Table structure for table `hl_users_status_updates` */



CREATE TABLE `hl_users_status_updates` (
`id_status` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_user` bigint(20) unsigned NOT NULL,
`status` varchar(254) NOT NULL,
`date` int(10) unsigned NOT NULL,
PRIMARY KEY (`id_status`),
KEY `id_user` (`id_user`)
) ENGINE=MyISAM AUTO_INCREMENT=673 DEFAULT CHARSET=latin1;



/*Table structure for table `hl_users_votes` */



CREATE TABLE `hl_users_votes` (
`id_vote` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_from` bigint(20) unsigned NOT NULL,
`id_to` bigint(20) unsigned NOT NULL,
`src` tinyint(4) NOT NULL COMMENT '1 - invitation, 2 - wall post, 3 - mail, 4 - other links, 5 - profile visit, 6 - twitter, 7 - linkedin',
`vote_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 - not voted, 1 - voted, 2 - denied',
`date` int(10) unsigned NOT NULL,
PRIMARY KEY (`id_vote`),
UNIQUE KEY `id_from` (`id_from`,`id_to`),
KEY `id_to` (`id_to`),
KEY `id_from index` (`id_from`)
) ENGINE=MyISAM AUTO_INCREMENT=1977 DEFAULT CHARSET=latin1;

i was a bit concerned when i saw "Impossible WHERE noticed after reading const tables" for the subquery on the ap table

however, some research revealed that this is actually a good thing

in any case, the subquery on the ap table is fine as written

any slowness in the query is likely coming from the su subquery, so let's rewrite that as a join
try that and see if it gets better (do another EXPLAIN here)

another optimization that should improve performance is to change a couple of your indexes, on the v and r tables, which require not only a join but a search based on an additional column

for the hl_users_votes table, drop the `id_from index` index, and add it back with the vote_status column added:
do the same thing for the hl_users_recommendations table, adding reccomend_status to the `id_from index` index

this will make both of these indexes a covering index for this query

do another EXPLAIN to see the effect

thanks for your comments, let me try and update