How to Index and Fasten This Slow Query?

demods · #1 (**permalink**) 03-02-10, 09:21

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

pootle flump · #2 (**permalink**) 03-02-10, 09:37

Not an ANSI SQL question this - moving to correct forum. NEEEEEOOOOOOOOOOOWW.

demods · #3 (**permalink**) 03-03-10, 07:20

Quote:

Originally Posted by pootle flump

Not an ANSI SQL question this - moving to correct forum. NEEEEEOOOOOOOOOOOWW.

Oops, sorry for that. Thanks for moving to correct forum..

mike_bike_kite · #4 (**permalink**) 03-03-10, 08:10

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.

demods · #5 (**permalink**) 03-03-10, 08:47

Quote:

Originally Posted by mike_bike_kite

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.

mike_bike_kite · #6 (**permalink**) 03-03-10, 10:33

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?

demods · #7 (**permalink**) 03-03-10, 11:35

Quote:

Originally Posted by mike_bike_kite

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

Quote:

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.

mike_bike_kite · #8 (**permalink**) 03-03-10, 13:06

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?

demods · #9 (**permalink**) 03-03-10, 14:34

Quote:

Originally Posted by mike_bike_kite

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;

Quote:

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.

Quote:

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.

Quote:

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

mike_bike_kite · #10 (**permalink**) 03-04-10, 08:26

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

demods · #11 (**permalink**) 03-04-10, 16:38

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

Internet Services

Web Development

Digital Marketing

Consumer Tech