Professional Web Applications Themes

Help in optimizing a slow query... - MySQL

Hello, I'm trying since weeks (with no luck...) to optimize a query which is "killing" my site on peak times (100+ visitors simultaneously). I almost gave up.... I really hope someone more expert than me here can give me some help... Here's the "slow" query: SELECT u.user_id, u.user_nickname, u.user_gender, u.user_province FROM users AS u LEFT JOIN photos AS p ON u.user_id = p.photo_user_id WHERE u.user_id <> 0 AND u.user_id = p.photo_user_id AND p.photo_approvestatus = 1 GROUP BY p.photo_user_id ORDER BY u.user_registrationdate LIMIT 0, 20 (I used the "GROUP BY" option because I need to group the result by the "p.photo_user_id" ...

  1. #1

    Default Help in optimizing a slow query...

    Hello,

    I'm trying since weeks (with no luck...) to optimize a query which is
    "killing" my site on peak times (100+ visitors simultaneously). I almost
    gave up.... I really hope someone more expert than me here can give me some
    help... Here's the "slow" query:

    SELECT u.user_id, u.user_nickname, u.user_gender, u.user_province
    FROM users AS u
    LEFT JOIN photos AS p ON u.user_id = p.photo_user_id
    WHERE u.user_id <> 0
    AND u.user_id = p.photo_user_id
    AND p.photo_approvestatus = 1
    GROUP BY p.photo_user_id
    ORDER BY u.user_registrationdate
    LIMIT 0, 20

    (I used the "GROUP BY" option because I need to group the result by the
    "p.photo_user_id" column where I have duplicated USER ID)





    If I try to use the EXPLAIN command here's what I get as result:

    ---------------------------------------------------------------------------
    SIMPLE p range photo_user_id_approvestatus_time,
    photo_approvestatus_time photo_approvestatus_time 1 NULL
    8032 Using where; Using temporary; Using filesort

    SIMPLE u eq_ref PRIMARY PRIMARY 3
    mydatabase.p.photo_user_id 1 Using where
    ---------------------------------------------------------------------------





    My table's structure looks like this:

    ---------------------------------------------------------------------------
    CREATE TABLE `users` (
    `user_id` mediumint(8) unsigned NOT NULL auto_increment,
    `user_name` varchar(20) NOT NULL default '',
    `user_gender` tinyint(1) unsigned NOT NULL default '0',
    `user_province` varchar(50) NOT NULL default '0',
    `user_registrationdate` int(11) unsigned NOT NULL default '0',
    PRIMARY KEY (`user_id`),
    KEY `user_registrationdate` (`user_registrationdate`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1 AUTO_INCREMENT=12726 ;


    CREATE TABLE `photos` (
    `photo_id` mediumint(8) unsigned NOT NULL auto_increment,
    `photo_user_id` mediumint(8) unsigned NOT NULL default '0',
    `photo_description` varchar(35) NOT NULL default '',
    `photo_time` int(11) unsigned NOT NULL default '0',
    `photo_approvestatus` tinyint(1) unsigned NOT NULL default '0',
    PRIMARY KEY (`photo_id`),
    KEY `photo_user_id_approvestatus_time`
    (`photo_user_id`,`photo_approvestatus`,`photo_time `),
    KEY `photo_approvestatus_time` (`photo_approvestatus`,`photo_time`),
    KEY `photo_time` (`photo_time`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1 AUTO_INCREMENT=23676 ;
    ---------------------------------------------------------------------------




    Does anyone have any idea on how to optimize this query and make it faster?
    How to get ride of the evil "Using temporary; Using filesort" ?

    Thank you for any help!


    DDJ Guest

  2. #2

    Default Re: Help in optimizing a slow query...

    DDJ wrote:
    > Hello,
    >
    > I'm trying since weeks (with no luck...) to optimize a query which is
    > "killing" my site on peak times (100+ visitors simultaneously). I almost
    > gave up.... I really hope someone more expert than me here can give me some
    > help... Here's the "slow" query:
    >
    > SELECT u.user_id, u.user_nickname, u.user_gender, u.user_province
    > FROM users AS u
    > LEFT JOIN photos AS p ON u.user_id = p.photo_user_id
    > WHERE u.user_id <> 0
    > AND u.user_id = p.photo_user_id
    > AND p.photo_approvestatus = 1
    The first thing I notice is that you're using LEFT JOIN (which is
    usually more expensive than INNER JOIN), yet you're WHERE conditions
    require that columns in p be non-NULL. So the "outer" case of the LEFT
    JOIN (where no row in p matches the given row in u) is eliminated from
    the query. You might as well use an INNER JOIN and that'll help speed
    up the query.

    You also have no columns from p in the select-list, so now I'm not sure
    why you're joining with that table at all.

    Also, somehow the optimizer isn't using the index you have defined over
    (`photo_user_id`,`photo_approvestatus`,`photo_time `).
    > GROUP BY p.photo_user_id
    > ORDER BY u.user_registrationdate
    > LIMIT 0, 20
    When you have different columns in the GROUP BY vs. the ORDER BY clause,
    this results in a temporary table and filesort.
    > (I used the "GROUP BY" option because I need to group the result by the
    > "p.photo_user_id" column where I have duplicated USER ID)
    Why group by the unique key of u, when you have no aggregate functions
    on any columns of p? I guess you're trying to get the same effect as
    DISTINCT.

    I'd try something like this to see if it gets a more favorable EXPLAIN
    report and better net performance:

    SELECT u.user_id, u.user_nickname, u.user_gender, u.user_province
    FROM users AS u
    WHERE EXISTS(
    SELECT 1 FROM photos AS p
    WHERE u.user_id = p.photo_user_id
    AND p.photo_approvestatus = 1)
    ORDER BY u.user_registrationdate
    LIMIT 0, 20

    I took out the user_id <> 0 because inequalities are unlikely to get the
    optimizer to use an index for it. It would be better to use user_id >
    0, assuming your user_id's don't go into negative numbers.

    Using a subquery requires MySQL 4.1 or later. You don't state which
    version you have. Though I do appreciate that you showed the DDL for
    your tables, which is something folks asking questions on the newsgroup
    seldom do!

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Help Optimizing Query
    By elDonrico in forum Coldfusion Database Access
    Replies: 15
    Last Post: May 30th, 11:15 PM
  2. Tools for optimizing mysql query
    By R. Rajesh Jeba Anbiah in forum MySQL
    Replies: 20
    Last Post: February 15th, 08:25 PM
  3. Slow MySql query
    By jack in forum PHP Development
    Replies: 2
    Last Post: August 31st, 08:44 PM
  4. Optimizing Query
    By Erwin in forum IBM DB2
    Replies: 2
    Last Post: August 14th, 03:07 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139