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

Filipe Tomita wrote:
> Hi all,

I would suspect that your problems may be more from your subselects than
from your joins.

Make sure all your join fields can be accessed using an index and change the
subselects to joins too.