Agreed that
1. indexing l.payment_date should help,
1a. furthermore, making the date criteria to be a range expression might
help, something like l.payment_date between date_format
('2007-12-17','%Y%m%d') and date_format('2007-12-18','%Y%m%d')
2. indexing interest_paid, misc_fees_paid, etc. shouldn't help,
3. replace left join with inner join whenever functionally it achieves the
same effect.Couple more thoughts: 1. You could use the "explain" tool to try out the tweak of your query. Basically you can run "explain" your query against the actual production database, and mysql should tell you an how it plans to run the query and a relative estimate on the time (and other cost estimate). It will require some more db knowledge to understand the output, but at the minimal you should be able to tell whether a tweak is expected to be doing better or worse: http://dev.mysql.com/doc/refman/5.0/en/explain.html 2. running a four-way join across a large dataset is bound to be challenging in general. Is there some workaround you can use to reduce the number of joins? For example, it looks like the only reason you need to join to prod_offering is to get the name to be displayed. The query might be much faster if you can have the query to return prd_id (and possibly you do some post processing to get the name) and thereby removing one join. Select la.prd_offering_id // return the id instead of the name sum(l.principal_paid)/100000 as Actualprincipal, sum(l.interest_paid)/100000 as InterestIncome, sum(l.misc_fees_paid)/100000 as EF from account a left join loan_account la on a.account_id = la.account_id left join loan_schedule l on la.account_id = l.account_id // remove the join WHERE date_format(l.payment_date,'%Y%m%d') = date_format('2007-12-11','%Y%m%d') and a.account_type_id = 1 group by la.prd_offering_id // now group by prdofferingid Again, you can use the explain query tool mentioned above to get an estimate of the gain before you run it. - sam On Dec 14, 2007 2:15 AM, Saurabh Kumar <[EMAIL PROTECTED]> wrote: > > > Hi Naganand, > > > > In the local database we don't have records are for testing. Anyway, here > are my suggestions on this Query for performance. > > > > > > 1) If we are indexing loan_schedule.payment_date, then definitely this > will improve the performance as it is used in the where clause. If > functional index is supporting then create it ( Date_Format( > payment_date) ), otherwise in the query replace Date_Format(l.payment_date) > with l.payment_date only for not suppressing the index. > > > > > > 2) Account table and Loan_account table have 'Account_Id' as primary > key. So use 'Inner Join' instead of 'left join' between these two tables. > > > > > > 3) As stated in the forwaded mail, indexing columns 'principal_paid, > interest_paid, misc_fees_paid won't improve performance because they are > not used in the where clause and thus won't have any impact on the query > performance. > > > > > > > > After indexing the column loan_schede.payment_date, the following query > will perform better. > > > > Select p.prd_offering_short_name as prdname, > > sum(l.principal_paid)/100000 as Actualprincipal, > > sum(l.interest_paid)/100000 as InterestIncome, > > sum(l.misc_fees_paid)/100000 as EF > > from account a > > *INNER join* loan_account la on a.account_id = la.account_id > > left join loan_schedule l on la.account_id = l.account_id > > left join prd_offering p on la.prd_offering_id = p.prd_offering_id > > WHERE > > date_format(l.payment_date,'%Y%m%d') = date_format('2007-12-17','%Y%m%d') > and a.account_type_id = 1 > > group by p.prd_offering_id > > > > > > *Thanks & Regards,* > > *Saurabh Kumar* • Developer • SunGard • Offshore Services • Divyasree > Chambers, Langford Road, Bangalore 560025 India > Tel +91-80-2222-0501 • Mobile +91-9886945575 • Fax +91-80-2222-0511 • > www.sungard.com > > -----Original Message----- > *From:* [EMAIL PROTECTED] [mailto: > [EMAIL PROTECTED] *On Behalf Of *Girish Naik > *Sent:* Friday, December 14, 2007 1:31 PM > *To:* [email protected] > *Subject:* Re: [Mifos-developer] Grameen Koota - Need Help with > queryoptimization > > > > Hi Naganand, > I dont have a huge DB for testing but I have following things might > help: > > · The columns 'principal_paid' , 'interest_paid' and > 'misc_fees_paid' in table 'loan_schedule' is not indexed, I think indexing > them may help for their fast retrieval and calculation. > > · In the where clause you are changing the date format in DB, > instead the date can be calculated in the code for current date and one day > before, and in query we can do a between current date and one day before > > > > Regards, > > *Girish Naik* > > > > From: "Naganand" <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> > > > > Date: Fri, 14 Dec 2007 10:56:04 > > To:"'Developer'" <[email protected]> > <[email protected]> > > Subject: [Mifos-developer] Grameen Koota - Need Help with query optimization > > > > > > Hi all, > > > > Need help on optimizing a query! > > > > Grameen Koota generates a eod report on the amount collected across all its > branches as loan repayment to calculate the repayment rate and loan portfolio > after the days outstanding. > > Currently GK is retrieving this data from "loan_schedule" table in mifos > which is a table with huge data. > > The query attached with this mail is being used to get the data mentioned > above. > > This query currently takes more than 12 min to run to the details on our 44 > branches database. Currently the no. of records in this table are 15532000 > records in the table. > > > > Can any body suggest us how do we optimize the query or is there any other > table to retrieve the data faster? Or if this is the minimal time? > > > > Sorry if the data provided is not clear enough! > > > > Need to run this everyday! > > > > Regards, > > > > Naganand > > AGM [Information Technology Department] > > > > Grameen Koota, > > Avalahalli, Anjanapura Post > > Bangalore- 560062 > > Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> <[EMAIL PROTECTED]> > > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> <[EMAIL PROTECTED]> > > Phone: 080-28436838, > > Mob: 9341940803 > > Website: www.grameenkoota.org <http://www.grameenkoota.org> > <http://www.grameenkoota.org> > > > > > > > > ------------------------------------------------------------------------- > > SF.Net email is sponsored by: > > Check out the new SourceForge.net Marketplace. > > It's the best place to buy or sell services > > for just about anything Open Source. > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace > > > > > ------------------------------------------------------------------------- > SF.Net email is sponsored by: > Check out the new SourceForge.net Marketplace. > It's the best place to buy or sell services > for just about anything Open Source. > > http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace >
------------------------------------------------------------------------- SF.Net email is sponsored by: Check out the new SourceForge.net Marketplace. It's the best place to buy or sell services for just about anything Open Source. http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
