Hi,
Your English is fine :) Your queries don't look too bad. It could be
there are no good indexes. Have you tried running EXPLAIN on them?
What version of MySQL are you using? You can also try profiling the
queries (by hand with SHOW STATUS, or more easily with MySQL Query
Profiler) to see what they are doing. I don't know what your operating
system is, but if it's Linux or UNIX you can also run "vmstat -n 5"
while the query runs so you can see what it is doing. (For example, it
may be swapping, which is bad).
If you need help understanding EXPLAIN, please write back and include
the output of SHOW CREATE TABLE for the tables, your query, and the
result of EXPLAIN for the query.
[ Triadbrasil ] Filipe Tomita wrote:
Hi all,
First sorry my bad english :)
I having a problem with a large join with 10 tables with 70Gb of text data,
some joins executed by index but some others not.
I´m work with HP SERVER (Proliant NL-150) a 2 Xeon 2 Duo with 3Gb Ram and
RAID 0.
When executed to a client with small datasets the retrive is fastest, but
when i try with a large dataset client the database down or left a 10 min to
execute a query.
This is my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
tmp_table_size =256M
key_buffer_size = 750M
max_allowed_packet = 10M
max_connections=400
table_cache = 4000
sort_buffer_size = 100M
read_buffer_size = 100M
read_rnd_buffer_size = 50M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_type=1
query_cache_size = 256M
query_cache_limit=25M
join_buffer_size=128M
thread_concurrency = 16
log-bin=mysql-bin
server-id = 1
innodb_buffer_pool_size = 1512M
innodb_additional_mem_pool_size = 100M
innodb_thread_concurrency=16
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
and that is a trouble SQL
####### SQL 1 ######
SELECT NAC.id, NAC.nome assunto, NAC.ordem
FROM Noticias N
INNER JOIN (
SELECT NC.noticiaId, A.id, A.nome, AC.ordem
FROM NoticiasClientes NC
INNER JOIN (AssuntosClientes AC, Assuntos A)
ON (NC.clienteId = '".$clienteId."'
AND NC.clienteId = AC.clienteId
AND NC.assuntoId =
AC.assuntoId
AND AC.assuntoId = A.id)
)NAC ON (N.dataInsercao = '".$clippingDate."')
######## SQL 2 #######
SELECT NT.id, NT.titulo as tituloNoticia, NC.tipo tipoNoticia, NI.id Imagem,
VI.nome AS veiculoNome, VI.id veiculoId, NC.impactoId
FROM (SELECT NCL.* FROM NoticiasClientes
NCL WHERE NCL.assuntoId = '".$filter."' AND NCL.clienteId='".$clienteId."')
NC
INNER JOIN (Noticias NT, Veiculos
VI, (SELECT VCL.veiculoId, VCL.clienteId, VCL.ordem
FROM VeiculosClientes VCL
WHERE VCL.clienteId='".$clienteId."'
ORDER BY VCL.ordem) VC)
ON (NT.id = NC.noticiaId
AND NT.dataInsercao =
'".$clippingDate."'
AND
VI.tipoVeiculoIdIN (".$tiposVeiculos.")
AND VI.id =
NT.veiculoId
)
LEFT JOIN (ImagemNoticia NI)
ON (NI.noticiaId = NC.noticiaId)
GROUP BY NC.noticiaId
######## SQL 3 #######
SELECT N.id, N.titulo,VCT.id veiculoId, VCT.veiculo, VCT.tipoVeiculo,
VCT.ordemVeiculo, NAC.assuntoId, NAC.impactoId, NAC.assunto,
NAC.ordemAssunto, IMN.id as imgId
FROM (Noticias N
INNER JOIN ((SELECT NC.noticiaId,
I.id as impactoId, A.nome as assunto, AC.ordem as ordemAssunto, AC.assuntoId
FROM
NoticiasClientes NC
INNER JOIN
(AssuntosClientes AC, Assuntos A, Impactos I)
ON (
NC.clienteId = '".$clienteId."'
AND
NC.clienteId = AC.clienteId
AND
NC.assuntoId = AC.assuntoId
AND AC.assuntoId = A.id
AND NC.impactoId = I.id)) NAC,
(SELECT V.id, V.nome as
Veiculo, VC.ordem as ordemVeiculo, TV.nome as tipoVeiculo
FROM Veiculos V
INNER JOIN
(VeiculosClientes VC, TiposVeiculos TV)
ON (
VC.clienteId = '".$clienteId."'
AND (
TV.id IN (".$tiposVeiculos."))
AND
V.id = VC.veiculoId
AND V.tipoVeiculoId = TV.id)) VCT)
ON (N.id = NAC.noticiaId AND
N.veiculoId = VCT.id))
LEFT JOIN ImagemNoticia IMN
ON (N.id = IMN.noticiaId)
WHERE
N.dataInsercao= '".$clippingDate."'
GROUP BY N.id
ORDER BY
VCT.tipoVeiculo, (VCT.ordemVeiculo & VCT.id), (NAC.ordemAssunto &
NAC.assuntoId), N.id
thank´s all.
Filipe Tomita
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
