COUNT with GROUP BY, JOIN and SUBQUERY very slow
This count query is very slow:
SELECT Count(*)
FROM (SELECT `t`.`id` AS `t0_c0`,
`t`.`regiao_id` AS `t0_c1`,
`t`.`nome` AS `t0_c2`,
`t`.`razao_social` AS `t0_c3`,
`t`.`cpf_cnpj` AS `t0_c4`,
`t`.`rg` AS `t0_c5`,
`t`.`inscricao_estadual` AS `t0_c6`,
`t`.`orgao_emissor` AS `t0_c7`,
`t`.`passaporte` AS `t0_c8`,
`t`.`data_nascimento` AS `t0_c9`,
`t`.`nome_mae` AS `t0_c10`,
`t`.`nome_pai` AS `t0_c11`,
`t`.`estado_id` AS `t0_c12`,
`t`.`cidade_id` AS `t0_c13`,
`t`.`ssid_id` AS `t0_c14`,
`t`.`mac` AS `t0_c15`,
`t`.`mac_vinculado` AS `t0_c16`,
`t`.`ccq` AS `t0_c17`,
`t`.`sinal` AS `t0_c18`,
`t`.`cep` AS `t0_c19`,
`t`.`endereco_bairro` AS `t0_c20`,
`t`.`endereco_rua` AS `t0_c21`,
`t`.`endereco_numero` AS `t0_c22`,
`t`.`endereco_complemento` AS `t0_c23`,
`t`.`endereco_latitude` AS `t0_c24`,
`t`.`endereco_longitude` AS `t0_c25`,
`t`.`endereco_usar_caixa_postal` AS `t0_c26`,
`t`.`endereco_numero_caixa_postal` AS `t0_c27`,
`t`.`coordenadas_verificadas` AS `t0_c28`,
`t`.`modo_pagamento_id` AS `t0_c29`,
`t`.`consta_spc_serasa` AS `t0_c30`,
`t`.`tipo_conexao` AS `t0_c31`,
`t`.`login` AS `t0_c32`,
`t`.`situacao_id` AS `t0_c33`,
`t`.`numero_bloqueio` AS `t0_c34`,
`t`.`plano_id` AS `t0_c35`,
`t`.`plano_valor_especial` AS `t0_c36`,
`t`.`cobranca_dia_id` AS `t0_c37`,
`t`.`senha` AS `t0_c38`,
`t`.`equipamento_comodato` AS `t0_c39`,
`t`.`ponto_cliente_id` AS `t0_c40`,
`t`.`ponto_numero` AS `t0_c41`,
`t`.`ip_fixo` AS `t0_c42`,
`t`.`modo_envio_cobranca` AS `t0_c43`,
`t`.`modo_envio_cobranca_outros_descricao` AS `t0_c44`,
`t`.`telefonia_ativa` AS `t0_c45`,
`t`.`isento` AS `t0_c46`,
`t`.`sistema_externo_id` AS `t0_c47`,
`t`.`revenda_id` AS `t0_c48`,
`t`.`usuario_id` AS `t0_c49`,
`t`.`data_tempo_ativacao` AS `t0_c50`,
`t`.`data_tempo` AS `t0_c51`,
`t`.`bemtevi_codcliente` AS `t0_c52`,
`t`.`bemtevi_endereco_rua` AS `t0_c53`,
`t`.`usar_endereco_bemtevi` AS `t0_c54`,
`t`.`bloquear_automaticamente` AS `t0_c55`,
`t`.`spc_serasa` AS `t0_c56`,
`endereco_instalacao`.`id` AS `t1_c0`,
`telefones`.`id` AS `t2_c0`,
`telefones`.`telefone` AS `t2_c3`,
`emails`.`id` AS `t3_c0`,
`emails`.`email` AS `t3_c3`,
`metodo_cobranca`.`id` AS `t4_c0`,
`acct`.`radacctid` AS `t5_c0`,
`acct`.`framedipaddress` AS `t5_c22`
FROM `radcliente` `t`
LEFT OUTER JOIN `radcliente_endereco_instalacao`
`endereco_instalacao`
ON ( endereco_instalacao.id = (SELECT id
FROM
`radcliente_endereco_instalacao`
`endereco_instalacao`
WHERE
(
endereco_instalacao.cliente_id =
t.id )
LIMIT 1) )
LEFT OUTER JOIN `radcliente_telefone` `telefones`
ON ( `telefones`.`cliente_id` = `t`.`id` )
LEFT OUTER JOIN `radcliente_email` `emails`
ON ( `emails`.`cliente_id` = `t`.`id` )
LEFT OUTER JOIN `radmetodo_cobranca` `metodo_cobranca`
ON ( metodo_cobranca.id = (SELECT id
FROM
`radmetodo_cobranca` `metodo_cobranca`
WHERE (
metodo_cobranca.cliente_id
=
t.id )
AND (
metodo_cobranca.arquivo =
'nao' )
ORDER BY
metodo_cobranca.id
DESC
LIMIT 1) )
LEFT OUTER JOIN `radacct` `acct`
ON ( acct.radacctid = (SELECT radacctid
FROM `radacct` `acct`
WHERE ( acct.cliente_id =
t.id )
ORDER BY radacctid DESC
LIMIT 1) )
GROUP BY t.id) sq
The keys are indexed, I tried also with composite and nothing keys, the part where further delay is in "sending data". I can not remove the joins as they are used where, removed to get better understanding.
This is the result explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7227 NULL
2 DERIVED t index PRIMARY,radius_fk_cliente_x_estado,radius_fk_cliente_x_cidade,radius_fk_cliente_x_cobranca_dia,radius_fk_cliente_x_plano,situacao_id,regiao_id,usuario_id,cpf_cnpj,login,situacao_id_2,nome PRIMARY 4 NULL 7227 NULL
2 DERIVED endereco_instalacao eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index
2 DERIVED telefones ref cliente_id cliente_id 4 radius.t.id 1 NULL
2 DERIVED emails ref cliente_id cliente_id 4 radius.t.id 1 NULL
2 DERIVED metodo_cobranca eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index
2 DERIVED acct eq_ref PRIMARY PRIMARY 8 func 1 Using where
5 DEPENDENT SUBQUERY acct ref cliente_id cliente_id 5 radius.t.id 1 Using where; Using index; Using filesort
4 DEPENDENT SUBQUERY metodo_cobranca ref cliente_id,arquivo cliente_id 5 radius.t.id 1 Using where; Using filesort
3 DEPENDENT SUBQUERY endereco_instalacao ref radius_fk_cliente_endereco_instalacao_x_cliente radius_fk_cliente_endereco_instalacao_x_cliente 5 radius.t.id 1 Using index
Notes:
- the table 'radacct' has 2 million records, a few hundred to 'radcliente'.
- The subquery in the join is to limit the join to one row, because I need the last only.