[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: optimizing a select statement over a database with >50 million recs
From:       karthik viswanathan <vkaru () iastate ! edu>
Date:       2004-02-29 23:41:07
Message-ID: 741172911040590 () webmail ! iastate ! edu
[Download RAW message or body]

Thanks for looking at my problem. here is the explain for this version of select

b | ref | id,p_id,combine | combine | 4 | const | 5281 | Using where; Using index;  
Using temporary | 
c | eq_ref | PRIMARY,p_id | PRIMARY | 4 | b.p_id | 1 |  |  | 
d | ref | name_id,out_id | out_id | 4 | c.out_id | 1 |  |  | 
a | ref | p_id | p_id | 4 | b.p_id | 3 | Using where | 
e | eq_ref | PRIMARY,start_id,combine,name_id | PRIMARY | 4 | d.name_id | 1 | 
Distinct | 
f | range | PRIMARY,name_id | PRIMARY | 4 | NULL | 2 | Using | where; | Distinct | 

Karthik


> Can you send us a new explain on this version?
> 
> Donny
> 
> > -----Original Message-----
> > From: karthik viswanathan [mailto:vkaru@iastate.edu]
> > Sent: Sunday, February 29, 2004 4:12 PM
> > To: mysql@lists.mysql.com
> > Subject: Re: optimizing a select statement over a database with >50
> > million recs
> > 
> > here is the updated select statement i came with but still needs
> > improvement
> > 
> > SELECT Distinct (a.id)
> > From table1 a
> > INNER JOIN table1 b
> > USING ( p_id )
> > INNER JOIN table2 c
> > USING ( p_id )
> > INNER JOIN table3 d
> > USING ( out_id )
> > INNER JOIN table4 e ON ( d.name_id = e.name_id )
> > INNER JOIN table4 f  ON ( e.start_id
> > BETWEEN f.left_id AND f.end_id )
> > WHERE (f.name_id =45 OR f.name_id =56)
> > AND b.id =275 AND a.id != b.id
> > 
> > This reduced the time from 0.5 secs to 0.3 secs but still i am looking for
> > some major
> > improvemnts. I am using this query in PHP and its been repeated several
> > times to
> > display a list. Any advice on this will be really useful. Is there
> > anything I could
> > do with the mysql and php configuration to increase the performance? The
> > server has
> > just 1GB ram, will adding more memory help?
> > 
> > Thanks
> > Karthik.
> > 
> > > Need help on optimizing the select statement:
> > >
> > > Table structure
> > >
> > > table1
> > > ------
> > > id
> > > p_id
> > >
> > > table2
> > > ------
> > > p_id
> > > out_id
> > >
> > > table3
> > > ------
> > > out_id
> > > name_id
> > >
> > > table4
> > > -------
> > > name_id (unique)
> > > prev_id
> > > start_id (unique)
> > > end_id (unique)
> > >
> > > Only table4 has unique fields all other fields are not unique.
> > >
> > > The following select statement does what is required but the tables are
> > really
> > > huge
> > > (> 50 million records) so need to be optimized
> > >
> > > SELECT DISTINCT (a.id)
> > > FROM table1 a, table1 b, table2 c, table3 d, table4 e, table4 f
> > > WHERE a.id =275 AND a.p_id = b.p_id AND a.id != b.id
> > >   AND a.p_id = c.p_id AND c.out_id = d.out_id AND d.name_id = e.name_id
> > >   AND e.start_id >= f.start_id AND e.end_id <= f.end_id AND e.end_id !=0
> > >   AND (f.name_id =45 OR f.name_id =56)
> > > GROUP BY b.id
> > >
> > > The explain for the above statement is
> > >
> > > a | ref | id,p_id,combine | combine | 4 | const | 5281 | Using where;
> > Using
> > > index;
> > > Using temporary; Using f...
> > > c | eq_ref | PRIMARY,p_id | PRIMARY | 4 | a.p_id | 1 |  |  |
> > > d | ref | name_id,out_id | out_id | 4 | b.out_id | 1 |  |  |
> > > b | ref | p_id | p_id | 4 | a.p_id | 3 | Using where |
> > > e | range | PRIMARY,start_id,end_id,combine,name_id | PRIMARY | 4 | NULL
> > | 2 |
> > > Using
> > > where |
> > > f | eq_ref | PRIMARY,start_id,end_id,combine,name_id | PRIMARY | 4 |
> > c.name_id |
> > 
> > > 1 |
> > > Using where |
> > >
> > > I am sure there should be some better way to do this using Inner join or
> > > something
> > > similar but I am not sure how. It will be helpful if you could suggest
> > me some
> > > improvements for this query. If you need any further explanation please
> > let me
> > > know.
> > >
> > > Thanks for your help
> > > Karu.
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:    http://lists.mysql.com/mysql?unsub=vkaru@iastate.edu
> > >
> > 
> > 
> > 
> > 
> > 
> > 
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=donny@intercosmos.com
> > 
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=vkaru@iastate.edu
> 






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]