How I can optimize this query?

[Thoms99]

Hi,

This query works fine but very slow...

SELECT DISTINCT STRAIGHT_JOIN
        news.event,news.user1,news.user2,news.time
    FROM news
    LEFT JOIN (
        SELECT friends.user1,friends.user2,friends.time
          FROM friends
         WHERE (friends.user1='Jhon' OR friends.user2='Jhon')
           AND friends.check='1'
    ) friends ON (
         news.user1=friends.user1 OR news.user1=friends.user2 OR
             news.user2=friends.user1 OR news.user2=friends.user2)
     WHERE news.time>friends.time
       AND news.user1!='Jhon'
       AND news.user2!='Jhon'
     ORDER BY news.id DESC LIMIT 6

Tables:

CREATE TABLE IF NOT EXISTS friends ( id int(20) NOT NULL AUTO_INCREMENT, user1 varchar(20) NOT NULL, user2 varchar(20) NOT NULL, check int(1) NOT NULL DEFAULT '0', time int(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT= 86258 ;

CREATE TABLE IF NOT EXISTS news ( id int(20) NOT NULL AUTO_INCREMENT, user1 varchar(30) NOT NULL, user2 varchar(30) NOT NULL, event longtext NOT NULL, time int(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT= 1893520 ;


have any idea?

[Luuk]

ALTER TABLE `friends`
ADD INDEX `f1` (`user1`) ,
ADD INDEX `f2` (`user2`) ;

might help....

also this might help:

ALTER TABLE `news`
ADD INDEX `t1` (`time`) ;

But its hard to tell without your data.... ;)

Please give output of 'EXPLAIN <query>' before and after...

[Thoms99]

it did not help...

output of explain:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	news 	ALL 	NULL	NULL	NULL	NULL	6742 	Using where; Using temporary; Using filesort
1 	PRIMARY 	<derived2> 	ALL 	NULL	NULL	NULL	NULL	1004 	Using where; Distinct; Using join buffer
2 	DERIVED 	friends 	ALL 	NULL	NULL	NULL	NULL	120089 	Using where

Thanks

[Luuk]

and what about the explain AFTER the indexes where added?
(the column, 'possible_keys' should list the key names...)

[Thoms99]

sorry, output after were added indexes

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	news 	ALL 	NULL	NULL	NULL	NULL	6790 	Using where; Using temporary; Using filesort
1 	PRIMARY 	<derived2> 	ALL 	NULL	NULL	NULL	NULL	1004 	Using where; Distinct; Using join buffer
2 	DERIVED 	friends 	index_merge 	f1,f2 	f1,f2 	62,62 	NULL	959 	Using union(f1,f2); Using where; Using index

Thanks

[Luuk]

ok, you also do check='1' so, drop the indexes, and re-create the indexes in friends as:

ALTER TABLE `friends` 
ADD INDEX `f1` (`user1`, `check`) , 
ADD INDEX `f2` (`user2`, 'check') ;

(again, please give the EXPLAIN output of this...

[Thoms99]

explain output of this:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	news 	ALL 	NULL	NULL	NULL	NULL	6883 	Using where; Using temporary; Using filesort
1 	PRIMARY 	<derived2> 	ALL 	NULL	NULL	NULL	NULL	1008 	Using where; Distinct; Using join buffer
2 	DERIVED 	friends 	ALL 	f1,f2 	NULL	NULL	NULL	120200 	Using where

[Luuk]

hmm, not enough input to give another try,
but if only only a few users have 'check'='1' than you could add this index:

ALTER TABLE `friends`  
ADD INDEX `f1` (`check`, `user1` )   
;

If that also does not help, i'm out of options ;)

[Rabbit]

Try using unions instead of all the ORs.
You don't need to subquery your friends table.
Don't use DISTINCT or ORDER BY unless you actually need it.
What is the STRAIGHT_JOIN for?

Also, you should think about normalizing your data. It will make querying easier.

More optimizations can be found in this article: http://bytes.com/topic/sql-server/in...ze-sql-queries

[Thoms99]

@Luuk- nope, and it did not help...

@Rabbit- DISTINCT and ORDER BY is necessary. Can you give examples for this query?

Thanks

[Luuk]

When trying to understand what you're actually querying...

I have this:
- in lines 5-8 you select all the friends of 'Jhon'
- in line 10-11 you select all friends, (INCLUDING the 'friends of friends' !) is this what you really want to do ?
- you are ordering on news.id. But i think news.time is better, because it wil show the first items to happen.

an example of what Rabbit means is something like:
(some of above things applied ;)

SELECT news.event,news.user1,news.user2,news.time 
    FROM news 
    LEFT JOIN friends f1 ON ( f1.check='1' AND news.time>f1.time AND 
					(news.user1=f1.user1 and f1.user1='Jhon' AND f1.user2=news.user2)  )
    LEFT JOIN friends f2 ON ( f2.check='1' AND news.time>f2.time AND 
				  (news.user2=f2.user2 and f2.user2='Jhon' AND f2.user1=news.user1)  )
     WHERE  news.user1!='Jhon' 
       AND news.user2!='Jhon' 
     ORDER BY news.time DESC LIMIT 6

[Thoms99]

I need to select all 'Jhon' friends and then select news by 'Jhon' friends on news.user1 or news.user2...

with your query I get all news including news not by Jhon friends..

[Rabbit]

You mean an example of using a union query instead of OR conditions? There are examples of that in the article I linked.

[Thoms99]

@Rabbit- But how I can use UNION in my query? Maybe you can give examples for my query?

Thanks

[Rabbit]

There are examples in the article I linked. Giving an "example" for your query would no longer be an example. It would just be the answer. Use the example in the article and make an attempt to reformulate your SQL to match the example and then post back here with the results.