RE: Copying to tmp table
Hi Caldi,
Can I knew what kind of table type you are using and also try to send me
the explain plan of the query execution.
I would like to know what kind of keys are being used.
Thanks Regards,
Dilipkumar
Mobile: +91-9884430998
E-mail: dilipkumar.par...@eds.com
-Original Message-
From: Carlos Eduardo Caldi [mailto:ce_ca...@hotmail.com]
Sent: Tuesday, April 28, 2009 8:19 PM
To: mysql@lists.mysql.com
Subject: Copying to tmp table
Hi friends
I have a proble with Copying to tmp table with large tables, table have
more than 3 million rows
EXPLAIN da Query :
Table : log0904
Type : ref
Possible_keys :
data_login_tplig,emp_grp_login_dtlig,grp_tiporeg_tipolig_dtlig,grp_dtlig
_tplig,dtlig_num,grp_equipe_emp_dtlig
Key : grp_tiporeg_tipolig_dtlig
Key_Len : 7
Ref : const
Rows : 11554
Extra : Using where; Using temporary; Using filesort
I kill my queries with more than 60 seconds
Time (sec) : 75
User : logs
Maquina : 192.168.0.76:43947
BD : logs
Thread : 18811390
State : Copying to tmp table
Query : SELECT tipo_atendimento
,login_operador,sum(duracao_operador) as duracao_operador, count(*)as
total FROM log0904 Where grupo = '1172' AND data_ligacao =
'2009-04-27' and hora_ligacao_ini between '17:00:00' and '17:59:59'
AND empresa = 'EXTREME-U' AND tipo_registro in ('L','T') GROUP BY
tipo_atendimento,login_operador
Somebody kwons a kind of tunning can I do?
Thanks
Carlos Caldi
_
Descubra seu lado desconhecido com o novo Windows Live!
http://www.windowslive.com.br
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Copying to tmp table
Sender: [EMAIL PROTECTED] Date: Tue, 24 Sep 2002 08:21:20 +0200 From: Lars Heidieker [EMAIL PROTECTED] on solaris it probably a good idea to change the tmp directory from /var/tmp - /tmp I recalled this feature and decided to look into linux for it's parallel. It's called tmpfs and probably requires a kernel build for most users (I have no idea what RedHat does nowdays, nor any of the popular distributions for that matter). In a kernel 'make xconfig', select the 'Virtual memory file system support' under the File systems tab. Add a mount in /etc/fstab along the lines of : tmpfs /var/tmptmpfs size=100M In my.cnf, adjust tmpdir tmpdir = /var/tmp -- You want to avoid mounting at /tmp for reasons noted in the tmpfs docs. There are additional reasons to avoid /tmp, just ensure the permissions on /var/tmp match /tmp. sql,query - 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
re: Copying to tmp table
Teh, Tuesday, September 24, 2002, 8:31:54 AM, you wrote: TCP Does anyone has any idea to improve this situation? TCP When I show my processlist, there is inner join query that having Copying TCP to tmp Table state, and it takes quite long time to finish the query. Any TCP idea to avoid Copying to tmp Table? I have tried to increase my TCP tmp_table_size however it does not remedy my problem. MySQL uses temporary tables in some queries. You can try to optimize your query. Here is some info that could help you: http://www.mysql.com/doc/en/EXPLAIN.html http://www.mysql.com/doc/en/Where_optimisations.html http://www.mysql.com/doc/en/ORDER_BY_optimisation.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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
Re: Copying to tmp table
hi, on solaris it probably a good idee to change the tmp directory from /var/tmp - /tmp as /tmp is a ram disk that gives a good performance boost if tmp tables are involved. The sort_buffer variable the the one to look at try increasing it, as if the result fits into the sort buffer no tmptable is needed... Teh Chee Peng wrote: hi, Does anyone has any idea to improve this situation? When I show my processlist, there is inner join query that having Copying to tmp Table state, and it takes quite long time to finish the query. Any idea to avoid Copying to tmp Table? I have tried to increase my tmp_table_size however it does not remedy my problem. Thanks in advance. - 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 -- Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, daß sie noch nicht einmal oberflächlich sind. --Friedrich Nietzsche - 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
RE: copying to tmp table
Paste EXPLAIN SELECT ... (your SELECT); from the mysql client. Hi! When I join 4 tables with about 3000 records in each mysql seems to work forever on copying to tmp table. I wonder what that could be. I have enough free space in the tmp-dir and don't know what else could it be. The database-model is normalized and on another machine it takes me about 1 second to get a response. The strange thing about it is, that it worked before. - 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
