mysql - How can I optimize the query below which uses three levels of select statements? -

How to customize the following query:

I have two tables, 'calendar_table' and 'consume 'I use this query to calculate monthly consumption here every year.

The calendar table contains days, months and years for the year 2005-2006 and consumption data for the monthly bill cycle has been billed in the consumption table. This query calculates the number of days for each bill and Use that will be consumed for each month.

  Select ID, bill_start_date as date_from, old, MONTHNAME (STR_TO_DATE (as theMonth as theyear, '% m')) on average, sum (DaysOnBill), TotalDaysInTheMonth, yoga (perDayConsumption * DaysOnBill) EstimatedConsumption the (calculation of a number (*) selected to display the month as the DaysOnBill IDs, date_from, theyear, theMonth, # use theMonth, TotalDaysInTheMonth, the perDayConsumption (c.id select, date_from, ct.dt, as theyear y, month theMonth, (Day LAST_DAY (ct.dt)) TotalDaysInTheMonth, c.date_from Su as as as perDayConsumption consumption Form includes several internal as calendar_table When the whistle on Ct.dt & gt; = c. The date_from and ct.dt & lt; = c.date_to) based on the ID as allDates Group, date_from, theyear, theMonth) ID Based on estimate group, theyear, theMonth;  

Approximately 1000 seconds to go through approximately 1 million records. Something can be done to make it faster?

itemprop = "text">

query A group is making a doubtful claim to do so before and after that on the other Build with, which is not really the case.

The first bill is added to all its days, then we are getting a monthly view on the data from the bill and group data such as month and year. It can be done in one pass, but the query is being included in the first and then the result is used as a derived table which is collected and collected. After all results are taken again and "to build another" group, which is already in the first (more bills month and year) and some pseudo Agrigrejeshn (eg. Yoga (daily Kansmoshn * Dejonbil) which Daily consonance is similar to *. Yoga of DaysOnBill, as a record only here)

It can be written as simply:

  C.id, bill_start_date as c.date_from, ct.y year, MONTHNAME (STR_T) As Tiotioditii (Sitimmti, '% M')) month, day (today) as Dinonbil, DE (Elasdeediai (Siteediti)) Inmonth total teeth, Syuem (c. Dead Kansompshn) consumption as estimated Kansolisn As C INNER CNG, CTI, CTM. Join the group with c.date_from and c.date_to group on ct.dt with whistle.  

I do not know if it will be faster or if the MySQL optimizer does not see through your query and it blinks it in any way.