By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,145 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

How I can optimize this query?

P: 8
Hi,

This query works fine but very slow...

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT STRAIGHT_JOIN
  2.         news.event,news.user1,news.user2,news.time
  3.     FROM news
  4.     LEFT JOIN (
  5.         SELECT friends.user1,friends.user2,friends.time
  6.           FROM friends
  7.          WHERE (friends.user1='Jhon' OR friends.user2='Jhon')
  8.            AND friends.check='1'
  9.     ) friends ON (
  10.          news.user1=friends.user1 OR news.user1=friends.user2 OR
  11.              news.user2=friends.user1 OR news.user2=friends.user2)
  12.      WHERE news.time>friends.time
  13.        AND news.user1!='Jhon'
  14.        AND news.user2!='Jhon'
  15.      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?
Mar 31 '12 #1
Share this Question
Share on Google+
17 Replies


Expert 100+
P: 1,035
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE `friends`
  2. ADD INDEX `f1` (`user1`) ,
  3. ADD INDEX `f2` (`user2`) ;
  4.  
might help....

also this might help:
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE `news`
  2. ADD INDEX `t1` (`time`) ;
  3.  
But its hard to tell without your data.... ;)

Please give output of 'EXPLAIN <query>' before and after...
Mar 31 '12 #2

P: 8
it did not help...

output of explain:
Expand|Select|Wrap|Line Numbers
  1. id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
  2. 1     PRIMARY     news     ALL     NULL    NULL    NULL    NULL    6742     Using where; Using temporary; Using filesort
  3. 1     PRIMARY     <derived2>     ALL     NULL    NULL    NULL    NULL    1004     Using where; Distinct; Using join buffer
  4. 2     DERIVED     friends     ALL     NULL    NULL    NULL    NULL    120089     Using where
Thanks
Mar 31 '12 #3

Expert 100+
P: 1,035
and what about the explain AFTER the indexes where added?
(the column, 'possible_keys' should list the key names...)
Mar 31 '12 #4

P: 8
sorry, output after were added indexes

Expand|Select|Wrap|Line Numbers
  1. id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
  2. 1     PRIMARY     news     ALL     NULL    NULL    NULL    NULL    6790     Using where; Using temporary; Using filesort
  3. 1     PRIMARY     <derived2>     ALL     NULL    NULL    NULL    NULL    1004     Using where; Distinct; Using join buffer
  4. 2     DERIVED     friends     index_merge     f1,f2     f1,f2     62,62     NULL    959     Using union(f1,f2); Using where; Using index
Thanks
Mar 31 '12 #5

Expert 100+
P: 1,035
ok, you also do check='1' so, drop the indexes, and re-create the indexes in friends as:
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE `friends` 
  2. ADD INDEX `f1` (`user1`, `check`) , 
  3. ADD INDEX `f2` (`user2`, 'check') ;  
  4.  
(again, please give the EXPLAIN output of this...
Mar 31 '12 #6

P: 8
explain output of this:
Expand|Select|Wrap|Line Numbers
  1. id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
  2. 1     PRIMARY     news     ALL     NULL    NULL    NULL    NULL    6883     Using where; Using temporary; Using filesort
  3. 1     PRIMARY     <derived2>     ALL     NULL    NULL    NULL    NULL    1008     Using where; Distinct; Using join buffer
  4. 2     DERIVED     friends     ALL     f1,f2     NULL    NULL    NULL    120200     Using where
Mar 31 '12 #7

Expert 100+
P: 1,035
hmm, not enough input to give another try,
but if only only a few users have 'check'='1' than you could add this index:
Expand|Select|Wrap|Line Numbers
  1. ALTER TABLE `friends`  
  2. ADD INDEX `f1` (`check`, `user1` )   
  3. ;   
  4.  
If that also does not help, i'm out of options ;)
Mar 31 '12 #8

Rabbit
Expert Mod 10K+
P: 12,366
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
Mar 31 '12 #9

P: 8
@Luuk- nope, and it did not help...

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

Thanks
Apr 1 '12 #10

Expert 100+
P: 1,035
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 ;)
Expand|Select|Wrap|Line Numbers
  1. SELECT news.event,news.user1,news.user2,news.time 
  2.     FROM news 
  3.     LEFT JOIN friends f1 ON ( f1.check='1' AND news.time>f1.time AND 
  4.                     (news.user1=f1.user1 and f1.user1='Jhon' AND f1.user2=news.user2)  )
  5.     LEFT JOIN friends f2 ON ( f2.check='1' AND news.time>f2.time AND 
  6.                   (news.user2=f2.user2 and f2.user2='Jhon' AND f2.user1=news.user1)  )
  7.      WHERE  news.user1!='Jhon' 
  8.        AND news.user2!='Jhon' 
  9.      ORDER BY news.time DESC LIMIT 6 
  10.  
Apr 1 '12 #11

P: 8
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..
Apr 1 '12 #12

Rabbit
Expert Mod 10K+
P: 12,366
You mean an example of using a union query instead of OR conditions? There are examples of that in the article I linked.
Apr 1 '12 #13

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

Thanks
Apr 2 '12 #14

Rabbit
Expert Mod 10K+
P: 12,366
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.
Apr 2 '12 #15

P: 8
I have no ideas how to get results which I need using UNION in my query...

I think I disable this feature in my site, because this consume a lot of CPU resources. Anyway thanks for trying to help.
Apr 2 '12 #16

Rabbit
Expert Mod 10K+
P: 12,366
First of all, I don't think you can even disable the UNION syntax. Second, it doesn't consume more CPU resources, it should be quicker and therefore, consume less resources.

An example of a union versus an or is this:
Expand|Select|Wrap|Line Numbers
  1. -- OR syntax 
  2. SELECT someField 
  3. FROM someTable 
  4. WHERE otherField = 5 OR otherField = 6; 
  5.  
  6. -- UNION syntax 
  7. SELECT someField 
  8. FROM someTable 
  9. WHERE otherField = 5 
  10.  
  11. UNION ALL 
  12.  
  13. SELECT someField 
  14. FROM someTable 
  15. WHERE otherField = 6; 
You just need to modify it for your join.
Apr 2 '12 #17

Expert 100+
P: 1,035
about the use of OR and/or UNION
in this article it says:
Using UNION or OR depends on the scenario you are faced with. So you need to do your analyzing before selecting the appropriate method. Also, above the four scenarios are not all an exhaustive list of scenarios, I selected those for the broad description purposes only
and i agree with that conclusion....
Apr 2 '12 #18

Post your reply

Sign in to post your reply or Sign up for a free account.