On Thu, 20 Jun 2002, Javier Campoamor 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>
<snip>
> 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.
>
What about creating more indices, on (say) Bill.date, Hour.HourID or Call.Type?
> 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 <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
