Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Unanswered: How to optimize this query

    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
    Last edited by ilayasoft; 07-22-11 at 09:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    6
    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;
    Attached Thumbnails Attached Thumbnails explain.png  

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Code:
    SELECT up.id_user
         , up.fb_name
         , up.email
         , up.mobile
         , up.description
         , up.url_resume
         , up.date
         , up.fb_city
         , up.fb_country
         , su.status
         , ( SELECT COUNT(*) FROM hl_users_votes           AS v  WHERE v.id_from  = up.id_user AND vote_status = 1 )      AS followers
         , ( SELECT COUNT(*) FROM hl_users_recommendations AS r  WHERE r.id_from  = up.id_user AND recommend_status = 1 ) AS recomends
         , ( SELECT COUNT(*) FROM hl_users_social_profiles AS sp WHERE sp.id_user = up.id_user )                          AS social_profiles
         , ( SELECT COUNT(*) 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
    INNER
      JOIN ( SELECT id_user
                  , MAX(`date`) AS latest 
               FROM hl_users_status_updates 
             GROUP
                 BY id_user ) AS sx 
        ON sx.id_user = up.id_user 
    INNER
      JOIN hl_users_status_updates AS su
        ON su.id_user = sx.id_user
       AND su.`date` = sx.latest
    ORDER 
        BY p.percentage DESC
    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:
    Code:
    KEY `id_from index` ( id_from , vote_status )
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    6
    thanks for your comments, let me try and update

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •