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
