MySQL Forums :: Performance :: Performance with select query that joins two tables one with 45MM and another with 12MM


Advanced Search

Performance with select query that joins two tables one with 45MM and another with 12MM
Posted by: M Sundar ()
Date: February 18, 2009 08:00PM

We are running into a significant performance issue when we query two tables with some where clause statements.
One table has 45MM rows and another one has 12MM rows, the inner join brings around 2MM rows and the applies the where clause. Explain query says that it is using the right indexes but the query takes anywhere from 50 to 80 seconds. We are thinkging of using 5.1 readonly database keeping our master with 5.0.22. Will this help in query performance?

We also tested with a smaller dataset in which we reduced the 45MM rows table to 2MM rows that has only the join clause rows, but it did not improve the query time.

Any help would be aprreciated.
Thanks

Options: ReplyQuote


Subject Views Written By Posted
Performance with select query that joins two tables one with 45MM and another with 12MM 4016 M Sundar 02/18/2009 08:00PM
Re: Performance with select query that joins two tables one with 45MM and another with 12MM 2021 Vince Gatto 02/18/2009 08:53PM
Re: Performance with select query that joins two tables one with 45MM and another with 12MM 1937 aftab khan 02/19/2009 05:34AM
Re: Performance with select query that joins two tables one with 45MM and another with 12MM 2205 M Sundar 02/19/2009 05:17PM
Re: Performance with select query that joins two tables one with 45MM and another with 12MM 2376 Rick James 02/20/2009 12:28AM
Re: Performance with select query that joins two tables one with 45MM and another with 12MM 1599 M Sundar 05/01/2009 10:12AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.