Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    Unanswered: How to Index and Fasten This Slow Query?

    I'm trying to use AjaxIM (ajax im - the ajax-based instant messenger) for an online project. The project has around 50000 users (and growing) and querying that database takes some serious time (more than 5-6secs).

    First let me give the table structures;

    Code:
    CREATE TABLE `ajaxim_users` (
    	`username` VARCHAR(32) DEFAULT NULL,
    	`password` VARCHAR(32) DEFAULT NULL,
    	`email` TEXT,
    	`is_online` INTEGER(11) DEFAULT '0',
    	`last_ping` TEXT,
    	`last_ip` VARCHAR(15) DEFAULT NULL,
    	`banned` TINYINT(1) DEFAULT '0',
    	`admin` TINYINT(1) DEFAULT '0',
    	`buddyicon` VARCHAR(4) NOT NULL DEFAULT 'none',
    	`profile` TEXT,
    	`locale` VARCHAR(10) NOT NULL DEFAULT '',
    	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`is_teb_user` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0',
    	UNIQUE KEY `id` (`id`),
    	UNIQUE KEY `username` (`username`)
    )ENGINE=InnoDB
    AUTO_INCREMENT=3 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
    
    CREATE TABLE `ajaxim_blocklists` (
    	`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
    	`user` VARCHAR(100) NOT NULL DEFAULT '',
    	`buddy` VARCHAR(100) NOT NULL DEFAULT '',
    	PRIMARY KEY (`id`),
    	KEY `user` (`user`, `buddy`)
    )ENGINE=MyISAM
    AUTO_INCREMENT=1 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
    
    CREATE TABLE `ajaxim_buddylists` (
    	`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
    	`user` VARCHAR(100) NOT NULL DEFAULT '',
    	`buddy` VARCHAR(100) NOT NULL DEFAULT '',
    	`group` VARCHAR(100) NOT NULL DEFAULT '',
    	PRIMARY KEY (`id`),
    	KEY `user` (`user`, `group`),
    	KEY `buddy` (`buddy`)
    )ENGINE=MyISAM
    AUTO_INCREMENT=17 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci';
    And the query that runs very slow is;

    Code:
    SELECT username, is_online
    FROM ajaxim_users
    WHERE (
    	(
    		username
    		IN (
    			SELECT user
    			FROM ajaxim_buddylists
    			WHERE buddy = 'berdem'
    		)
    		AND username NOT 
    		IN (
    			SELECT buddy
    			FROM ajaxim_blocklists
    			WHERE user =  'berdem'
    		)
    	)
    	OR username
    	IN (
    		'admin'
    	)
    )
    GROUP BY username;
    That query takes more than 5-6secs to give a result. And here is the result after explaining the query;

    Code:
    id  |  select_type        |  table              |  type  |  possible_keys  |  key    |  key_len  |  ref    |  rows  |  Extra
    ---------------------------------------------------------------------------------------------------------------------------------------------------------
    1   | PRIMARY             |  ajaxim_users       |  ALL   |  username       |  NULL   |  NULL     |  NULL   |  44089 |  Using where; Using temporary; Using filesort
    3   | DEPENDENT SUBQUERY  |  NULL               |  NULL  |  NULL           |  NULL   |  NULL     |  NULL   |  NULL  |  Impossible WHERE noticed after reading const table...
    2   | DEPENDENT SUBQUERY  |  ajaxim_buddylists  |  ref   |  buddy          |  buddy  |	102      |  const  |  1     |  Using where
    There is an index named 'username' on 'ajaxim_users' but according to the explain result, MySQL doesn't use this index.

    I really don't know how to index and fasten this query. I must use the same query but implement some index mechanism.

    If anyone can provide me some info, I'd be very happy.

    Thanks...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not an ANSI SQL question this - moving to correct forum. NEEEEEOOOOOOOOOOOWW.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by pootle flump View Post
    Not an ANSI SQL question this - moving to correct forum. NEEEEEOOOOOOOOOOOWW.
    Oops, sorry for that. Thanks for moving to correct forum..

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by demods
    I really don't know how to index and fasten this query. I must use the same query but implement some index mechanism.

    If anyone can provide me some info, I'd be very happy.
    Try something like :
    Code:
    SELECT u.username, u.is_online
    FROM   ajaxim_users u
    WHERE  u.username = 'admin'
           or exists (
                select 1
                FROM ajaxim_buddylists bud
                WHERE bud.buddy = 'berdem'
                     and bud.user = u.username )
           and NOT exists (
                select 1
    	    FROM ajaxim_blocklists blk
                WHERE blk.user =  'berdem'
                        and blk.buddy = u.username );
    Mike

    EDIT : Just a few small points:
    • Admin appears to be a boolean field in the main user table rather than a user with the name 'admin' - not sure if this is a bug or not.
    • The buddy table has reference to a group but you don't use this in your SQL - not sure if you should be doing this.
    • Not sure whether you should also need to use the banned flag in the user table.
    • The tables all have numeric id fields but these fields aren't referenced in the buddy and block tables - it uses the user name instead, so is there a point to having the id fields at all?
    • Fasten just means to attach one thing to another.
    Last edited by mike_bike_kite; 03-03-10 at 08:46. Reason: added a few small points

  5. #5
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by mike_bike_kite View Post
    Try something like :
    Code:
    SELECT u.username, u.is_online
    FROM   ajaxim_users u
    WHERE  u.username = 'admin'
           or exists (
                select 1
                FROM ajaxim_buddylists bud
                WHERE bud.buddy = 'berdem'
                     and bud.user = u.username )
           and NOT exists (
                select 1
    	    FROM ajaxim_blocklists blk
                WHERE blk.user =  'berdem'
                        and blk.buddy = u.username );
    Mike
    Thanks Mike, but after trying your SQL, nothing had changed. Still taking at least 3-4secs to execute the query.

    Here are the EXAMINE results for your query.

    Code:
    id  |  select_type         |  table  |  type  |  possible_keys  |  key   |  key_len  |  ref   |  rows   |  Extra
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   |  PRIMARY             |  u      |  ALL   |  username       |  NULL  |  NULL     |  NULL  |  44170  |  Using where
    3   |  DEPENDENT SUBQUERY  |  NULL   |  NULL  |  NULL           |  NULL  |  NULL     |  NULL  |  NULL   |  Impossible WHERE noticed after reading const table...
    2   |  DEPENDENT SUBQUERY  |  bud    |  ref   |  buddy          |  buddy |  102      |  const |  1      |  Using where
    As you can see, still 44170 records are scanned on table "u" (ajaxim_users table). I'm not good at database performance and indexing, but I think I have to decrease row lookups. There is something wrong with the index and this really effects the performance.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by demods
    after trying your SQL, nothing had changed. Still taking at least 3-4secs to execute the query.
    I suppose I should of asked this first but what is the SQL supposed to do? I just took your SQL and tried to make it a bit more efficient. If it's just to show all the buddies of a user that aren't on his block list then why is admin in there?

    At least the last attempt made things a little bit faster but here's another random attempt to keep you amused in the mean time (that doesn't involve admin):
    Code:
    select bud.user, 
              ( select u.is_online 
                from ajaxim_users u
                where u.username = bud.user ) as is_online
    FROM ajaxim_buddylists bud
    WHERE bud.buddy = 'berdem'
            and NOT exists (
                select 1
    	    FROM ajaxim_blocklists blk
                WHERE blk.user =  'berdem'
                        and blk.buddy = bud.user );
    Mike

    PS : I assume the "Impossible WHERE noticed" is because there are no blocked users. The 44170 rows returned is definitely the main issue but I'd hoped to avoid that. Can I ask how many people is berdem a buddy of? How many admin users do you have?
    Last edited by mike_bike_kite; 03-03-10 at 10:39.

  7. #7
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by mike_bike_kite View Post
    I suppose I should of asked this first but what is the SQL supposed to do?
    Well I think that's my fault that I didn't tell what this SQL is doing. This query is populating the friendlist, like MSN Live or Yahoo Messenger. It looks for buddies and blocked users of a member (here 'berdem'), and ONLY gets buddy list. If there are no blocked user in the "ajaxim_blocklists" table, then the result returns nothing but only the buddy list.

    But the problem is that MySQL looks for every record on the "ajaxim_users" table.

    If we chance and simplify the query like below;

    Code:
    SELECT username, is_online
    FROM ajaxim_users
    WHERE (
    	(
    		username
    		IN (
    			SELECT user
    			FROM ajaxim_buddylists
    			WHERE buddy = 'berdem'
    		)
    	)
    )
    GROUP BY username;
    nothing changes. Because "ajaxim_blocklists" table has no records yet as no one blocked others. Also 'admin' part is not important as the script looks only if the member is the administrator, so it can be ignored too.

    Now the EXAMINE results for my new and tiny SQL query;

    Code:
    id  |  select_type         |  table              |  type  |  possible_keys  |  key    |  key_len  |  ref    |  rows   |  Extra
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1   |  PRIMARY             |  ajaxim_users       |  ALL   |  NULL           |  NULL   |  NULL     |  NULL   |  44180  |  Using where; Using temporary; Using filesort
    2   |  DEPENDENT SUBQUERY  |  ajaxim_buddylists  |  ref   |  buddy          |  buddy  |  102      |  const  |  1      |  Using where
    As you can see, "ajaxim_blocklists" table or 'admin' parts have no effect. It's the index part on the users table.

    BUT, if I change this query into

    Code:
    SELECT username, is_online
    FROM ajaxim_users
    WHERE username =  'berdem'
    GROUP BY username
    it successfully uses the index.

    Code:
    id  |  select_type  |  table         |  type   |  possible_keys  |  key       |  key_len  |  ref    |  rows  |  Extra
    ---------------------------------------------------------------------------------------------------------------------
    1   |  SIMPLE       |  ajaxim_users  |  const  |  username       |  username  |  303      |  const  |  1
    PS : I assume the "Impossible WHERE noticed" is because there are no blocked users. The 44170 rows returned is definitely the main issue but I'd hoped to avoid that. Can I ask how many people is berdem a buddy of? How many admin users do you have?
    'berdem' here have ONLY one buddy and no blocked users. There is also one 'admin' user in the system.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Why is the admin user involved in the query at all?

    What happened with the last query I gave?

    Other small points to add to the others:
    • Shouldn't the fields username in the main user table and user in the buddy and block tables be defined the same way? One's defined as VARCHAR(32) DEFAULT NULL, the others as VARCHAR(100) NOT NULL.
    • The id field is defined as bigint(20) - I assume this is to ensure that all beings in the known universe can have a separate id.
    • Why do you use group by at all in your SQL?

  9. #9
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by mike_bike_kite View Post
    Why is the admin user involved in the query at all?

    What happened with the last query I gave?

    Other small points to add to the others:
    • Shouldn't the fields username in the main user table and user in the buddy and block tables be defined the same way? One's defined as VARCHAR(32) DEFAULT NULL, the others as VARCHAR(100) NOT NULL.
    • The id field is defined as bigint(20) - I assume this is to ensure that all beings in the known universe can have a separate id.
    • Why do you use group by at all in your SQL?
    Wow, lots of questions to answer

    Well, first let me again say that this is not my script. This is, indeed, an open source script from Ajax IM. I'm just using the same script on my project. You are right at some points.

    Now the other questions;

    What happened with the last query I gave?
    It changed everything. Now it uses the correct index and the query takes only 0.0002secs to execute. But now I have to implement this query. It's OK, at least I now have the solution.

    Shouldn't the fields username in the main user table and user in the buddy and block tables be defined the same way? One's defined as VARCHAR(32) DEFAULT NULL, the others as VARCHAR(100) NOT NULL.
    You are right, that's my fault. They are all varchar(100), I pasted wrong SQL script in my first post.

    The id field is defined as bigint(20) - I assume this is to ensure that all beings in the known universe can have a separate id.
    You are again right. But as I said before, this is an open source script and I thought there might be something that the author thought of. Now I'm converting all "bigint" types to "mediumint" types.

    Now that you are right for all of your questions, all I wonder is that why the PRIMARY SELECT doesn't use any index on my first query. That seems like the root of all problems. Please correct me if I'm wrong, but if I have the chance to make the first PRIMARY SELECT use the correct index and prevent to lookup 47000+ rows, the problem would be solved.

    Although your latest query solves everything, I'm looking for a solution with the original query, because I really don't know what side-effects of your query will be. But I will try to implement your query, and I hope everything goes fine.

    So, thank you so much for providing a very fast solution and opening my mind. I really learned so much.

    Thanks...

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by demods
    first let me again say that this is not my script. This is, indeed, an open source script from Ajax IM.
    It might be worth pointing the authors at this thread then. It wouldn't take much to improve things. You could even suggest they provide some example SQL to actually use the tables!

    Quote Originally Posted by demods
    I'm looking for a solution with the original query, because I really don't know what side-effects of your query will be.
    The simple thing would be to test the results of your original query against the results of the new query for various user names. If the results are the same then you should be able to just replace the SQL directly with no worries. Remember that the my query doesn't include the admin user but that was just because I couldn't see why it should. If you want to try and improve your original SQL then just Google for "MySQL performance tuning" and just devote some time to study the pages returned.

    Quote Originally Posted by demods
    You are right at some points.
    I must be loosing my touch

  11. #11
    Join Date
    Dec 2009
    Posts
    23
    Mike,

    I successfully implemented your query into my script and it works great. You saved my day. I really thank you so much. And I will also point the authors at this thread, as it helped me so much.

    Thanks again...

Tags for this Thread

Posting Permissions

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