Hola Javier,
It is difficult to tell because you did not include
the table structures, relationships, indices, etc.
But... at a first glance we is killing you query
anyways is this:
> FROM
> Bill b, Hour h
The poor db engine is probably creating a huge
temporary table with all possible combinations between
Bill and Hour. Is this really what you want to do?
Good Luck!
--- Javier Campoamor <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have a problem with the next query because it's
> too slow (>10 seconds) and
> I need to do 10 queries like this one to show a
> page.
>
> <QUERY>
>
> SELECT
> h.Hour, count(c.Amount) As NumberOfCalls,
> sum(c.Amount) As Amount
> FROM
> Bill b, Hour h
> LEFT JOIN
> Call c
> ON
> c.Type='N' AND
> b.date <= '2001-06-01' AND
> b.date >= DATE_SUB('2001-06-01', INTERVAL 1 MONTH)
> AND
> b.BillNumber = c.BillNumber AND
> h.HourId = HOUR(c.Hour)
> GROUP BY
> h.Hour
> ORDER BY
> h.HourId;
>
> </QUERY>
>
> In this query the Calls table is big (>70.000 rows)
> but the two other tables
> are really small (24 & 20 rows).
>
> When I try to show only the summary for the hours
> when there has been Calls
> (without the Hour table and the LEFT JOIN), it takes
> only 0,5 seconds but I
> need to use the Hour table to obtain also the
> summary for the hours when
> there hasn't been calls.
>
> I have already created index for the Call.BillNumber
> and the Bill.BillNumber
> fields, and it has helped (from 40 second to 10
> seconds) but it doesn't
> solve the problem.
>
> I though that the problem were in the HOUR(c.Hour)
> but I used a fixed value
> and the query is still slow.
>
> Do you have any idea about the way to optimize it?
>
> Thanks in advance
>
> Javier
>
>
>
>
---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list
> archive)
>
> To request this thread, e-mail
> <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try:
> http://lists.mysql.com/php/unsubscribe.php
>
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
