[EMAIL PROTECTED] wrote:
> Hi
>
> I need to prevent users from holding down the server,issuing the next
> query the server becomes so slow that the other clients can't even login!,
> the tables are around 6Million records:
>
> SELECT (some fields ), sum(more fields) AS calcfield
> FROM table1 INNER JOIN table2 ON ... INNER JOIN table3 ON ....
> GROUP BY (group field)
> HAVING table1.field = 'something' AND calcfield BETWEEEN 1 AND 10
>
> if the same query is changed to
>
> SELECT (some fields ), sum(more fields) AS calcfield
> FROM table1 INNER JOIN table2 ON ... INNER JOIN table3 ON ....
> WHERE table1.field = 'something'
> GROUP BY (group field)
> HAVING calcfield BETWEEEN 1 AND 10
>
> the query takes 3sec!
>
> Is there a way to handle this situations in witch one thread takes over
> the whole server??
HAVING clause is applied before data are sent to the client without any optimization.
That is why you first query is slower than second one.
Turn on slow query log to find queries that take a long time to execute:
http://www.mysql.com/doc/en/Slow_query_log.html
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
