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.tipoVeiculoId IN (".$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