> hi,
>
> I have pretty bad performance on the following query. Could
> someone help me to define the indexes I need on the tables to
> improve this.
> Thanks in advance.
>
> select a.field_1,a.date,b.field_1,b.field_2,sum(a.subtotal) as total
> from table_a as a, table_b as b
> group by b.field_2,a.field_1
> having month(a.date)=10 and a.field_1=b.field_1
> order by b.field_2 asc, a.date desc
First of all, move the statements in the HAVING clause
into a WHERE clause. They way you're writing this
statement defeats all optimizations MySQL might try
to do on the query.
Secondly, make sure you index field_1 in both tables.
Thirdly, there was a discussion of the month() function
being fairly slow one or two days ago, and some
people came up with alternate solutions. Check the
archives.
/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq
---------------------------------------------------------------------
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
