1

In a relationship one to many or one to one you can limit the relationship of the related table? For example, I have a table 'cliente' and another 'acct', each 'cliente' can have thousands (perhaps millions of 'acct'). In one instance I want to get 20 'cliente' and each 'cliente' to the latest 25 'acct'. The problem is limited so that each 'cliente' takes only 25 'acct', for example, if a 'cliente' has 1 million 'acct' will have to seek this 1 million to have only the last 25 'acct'. The problem of course, unnecessary burden. I believed I had found the solution to the problem with a subquery in the join, but created another that is in the count.

Some examples:

Yii:

$criteria = new CDbCriteria();
$criteria->select = 'id';
$criteria->with = [
    'acct' => [
        'select' => 'radacctid',
    ],
];

$criteria->together = true;

$criteria->group = 't.id';

$dataProvider = new CActiveDataProvider('Cliente', [
    'criteria' => $criteria,
]);

Generated SQL with standard Yii:

SELECT `t`.`id`           AS `t0_c0`, 
       `acct`.`radacctid` AS `t1_c0` 
FROM   `radcliente` `t` 
       LEFT OUTER JOIN `radacct` `acct` 
                    ON ( `acct`.`cliente_id` = `t`.`id` ) 
GROUP  BY t.id 
ORDER  BY radacctid DESC 
LIMIT  10 

Generated SQL with my solution (with this limit to one row not seeking millions of lines to have only the last 'acct'):

SELECT `t`.`id`           AS `t0_c0`, 
       `acct`.`radacctid` AS `t1_c0` 
FROM   `radcliente` `t` 
       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 
LIMIT  10 

The problem is in a count SQL. Currently the 'acct' table has 2 million rows and the query below takes 4 seconds even with the technique of limiting the relationship to one row only. And the more 'acct' table grows slower is as if the count is not limited to only row:

SELECT Count(*) 
FROM   (SELECT `t`.`id`           AS `t0_c0`, 
               `acct`.`radacctid` AS `t1_c0` 
        FROM   `radcliente` `t` 
               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 

Maybe my solution to limit the relationship is wrong and there is another way to do. Any suggestions?

UPDATE

Example with filter:

SELECT Count(*) 
FROM   (SELECT `t`.`id`           AS `t0_c0`, 
               `acct`.`radacctid` AS `t1_c0` 
        FROM   `radcliente` `t` 
               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) ) 
        WHERE  `acct`.`nasipaddress` = '172.21.10.17' 
        GROUP  BY t.id) sq 

SHOW CREATE TABLES:

CREATE TABLE `radcliente` 
  ( 
     `id`                                   INT(11) NOT NULL auto_increment, 
     `regiao_id`                            INT(11) DEFAULT NULL, 
     `nome`                                 VARCHAR(100) CHARACTER SET latin1 
     NOT NULL, 
     `razao_social`                         VARCHAR(45) CHARACTER SET latin1 
     DEFAULT NULL, 
     `cpf_cnpj`                             VARCHAR(20) CHARACTER SET latin1 NOT 
     NULL, 
     `rg`                                   VARCHAR(20) CHARACTER SET latin1 NOT 
     NULL, 
     `inscricao_estadual`                   VARCHAR(45) CHARACTER SET latin1 
     DEFAULT NULL, 
     `orgao_emissor`                        VARCHAR(20) CHARACTER SET latin1 NOT 
     NULL, 
     `passaporte`                           VARCHAR(100) DEFAULT NULL, 
     `data_nascimento`                      DATE NOT NULL, 
     `nome_mae`                             VARCHAR(100) CHARACTER SET latin1 
     NOT NULL, 
     `nome_pai`                             VARCHAR(100) CHARACTER SET latin1 
     NOT NULL, 
     `estado_id`                            INT(11) DEFAULT NULL, 
     `cidade_id`                            INT(11) DEFAULT NULL, 
     `ssid_id`                              INT(11) DEFAULT NULL, 
     `mac`                                  VARCHAR(45) CHARACTER SET latin1 
     DEFAULT NULL, 
     `mac_vinculado`                        VARCHAR(45) CHARACTER SET latin1 
     DEFAULT NULL, 
     `ccq`                                  TINYINT(3) DEFAULT NULL, 
     `sinal`                                TINYINT(3) DEFAULT NULL, 
     `cep`                                  VARCHAR(45) CHARACTER SET latin1 NOT 
     NULL, 
     `endereco_bairro`                      VARCHAR(45) CHARACTER SET latin1 NOT 
     NULL, 
     `endereco_rua`                         VARCHAR(45) CHARACTER SET latin1 NOT 
     NULL, 
     `endereco_numero`                      VARCHAR(45) CHARACTER SET latin1 NOT 
     NULL, 
     `endereco_complemento`                 VARCHAR(45) CHARACTER SET latin1 
     DEFAULT NULL, 
     `endereco_latitude`                    VARCHAR(45) CHARACTER SET latin1 
     DEFAULT NULL, 
     `endereco_longitude`                   VARCHAR(45) CHARACTER SET latin1 
     DEFAULT NULL, 
     `endereco_usar_caixa_postal`           TINYINT(4) NOT NULL DEFAULT '0', 
     `endereco_numero_caixa_postal`         INT(3) NOT NULL, 
     `coordenadas_verificadas`              TINYINT(1) NOT NULL DEFAULT '0', 
     `modo_pagamento_id`                    TINYINT(2) NOT NULL DEFAULT '1', 
     `consta_spc_serasa`                    TINYINT(1) NOT NULL DEFAULT '0', 
     `tipo_conexao`                         VARCHAR(20) CHARACTER SET latin1 NOT 
     NULL, 
     `login`                                VARCHAR(100) CHARACTER SET latin1 
     NOT NULL, 
     `situacao_id`                          INT(11) NOT NULL, 
     `numero_bloqueio`                      TINYINT(4) NOT NULL DEFAULT '0' 
     comment 
'Se o cliente estiver bloqueado, esse campo indica qual o numero do bloqueio do cliente. Se é o primeiro (1) ou o segundo (2).'
     , 
`plano_id`                             INT(11) DEFAULT NULL, 
`plano_valor_especial`                 VARCHAR(45) NOT NULL DEFAULT '0,00', 
`cobranca_dia_id`                      INT(11) DEFAULT NULL, 
`senha`                                VARCHAR(100) CHARACTER SET latin1 NOT 
     NULL, 
`equipamento_comodato`                 TINYINT(1) NOT NULL DEFAULT '1', 
`ponto_cliente_id`                     INT(11) DEFAULT '0', 
`ponto_numero`                         INT(11) DEFAULT NULL, 
`ip_fixo`                              VARCHAR(45) CHARACTER SET latin1 DEFAULT 
     NULL comment 
'IP fixo do cliente, se existir.', 
`modo_envio_cobranca`                  TINYINT(4) NOT NULL DEFAULT '1', 
`modo_envio_cobranca_outros_descricao` VARCHAR(100) NOT NULL, 
`telefonia_ativa`                      TINYINT(4) NOT NULL DEFAULT '0', 
`isento`                               TINYINT(1) NOT NULL DEFAULT '0', 
`sistema_externo_id`                   INT(11) DEFAULT NULL, 
`revenda_id`                           INT(11) NOT NULL DEFAULT '1', 
`usuario_id`                           INT(11) DEFAULT NULL, 
`data_tempo_ativacao`                  DATETIME DEFAULT NULL, 
`data_tempo`                           DATETIME NOT NULL, 
`bemtevi_codcliente`                   INT(11) DEFAULT NULL, 
`bemtevi_endereco_rua`                 VARCHAR(100) DEFAULT NULL, 
`usar_endereco_bemtevi`                INT(1) NOT NULL DEFAULT '1', 
`bloquear_automaticamente`             TINYINT(4) DEFAULT '1', 
`spc_serasa`                           ENUM('nenhum', 'spc', 'serasa', 
     'spceserasa') NOT NULL DEFAULT 
'nenhum', 
     PRIMARY KEY (`id`), 
     KEY `radius_fk_cliente_x_estado` (`estado_id`), 
     KEY `radius_fk_cliente_x_cidade` (`cidade_id`), 
     KEY `radius_fk_cliente_x_cobranca_dia` (`cobranca_dia_id`), 
     KEY `radius_fk_cliente_x_plano` (`plano_id`), 
     KEY `situacao_id` (`situacao_id`), 
     KEY `regiao_id` (`regiao_id`), 
     KEY `usuario_id` (`usuario_id`), 
     KEY `cpf_cnpj` (`cpf_cnpj`), 
     KEY `login` (`login`), 
     KEY `situacao_id_2` (`situacao_id`), 
     FULLTEXT KEY `nome` (`nome`, `razao_social`), 
     CONSTRAINT `radcliente_ibfk_1` FOREIGN KEY (`usuario_id`) REFERENCES 
     `radusuario` (`id`) ON DELETE no action ON UPDATE no action 
  ) 

engine=innodb auto_increment=7344 DEFAULT charset=utf8 


create TABLE `radacct` ( 
`radacctid` BIGINT(21) NOT NULL auto_increment, 
`acctsessionid` VARCHAR(64) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`acctuniqueid` VARCHAR(32) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`username` VARCHAR(64) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`groupname` VARCHAR(64) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`realm` VARCHAR(64) CHARACTER SET latin1 DEFAULT '', 
`nasipaddress` VARCHAR(15) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`nasportid` VARCHAR(15) CHARACTER SET latin1 DEFAULT NULL, 
`nasporttype` VARCHAR(32) CHARACTER SET latin1 DEFAULT NULL, 
`acctstarttime` datetime DEFAULT NULL, 
`acctstoptime` datetime DEFAULT NULL, 
`acctsessiontime` INT(12) DEFAULT NULL, 
`acctauthentic` VARCHAR(32) CHARACTER SET latin1 DEFAULT NULL, 
`connectinfo_start` VARCHAR(50) CHARACTER SET latin1 DEFAULT NULL, 
`connectinfo_stop` VARCHAR(50) CHARACTER SET latin1 DEFAULT NULL, 
`acctinputoctets` BIGINT(20) DEFAULT NULL, 
`acctoutputoctets` BIGINT(20) DEFAULT NULL, 
`calledstationid` VARCHAR(50) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`callingstationid` VARCHAR(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
`acctterminatecause` VARCHAR(32) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`servicetype` VARCHAR(32) CHARACTER SET latin1 DEFAULT NULL, 
`framedprotocol` VARCHAR(32) CHARACTER SET latin1 DEFAULT NULL, 
`framedipaddress` VARCHAR(15) CHARACTER SET latin1 NOT NULL DEFAULT '', 
`acctstartdelay` INT(12) DEFAULT NULL, 
`acctstopdelay` INT(12) DEFAULT NULL, 
`xascendsessionsvrkey` VARCHAR(10) CHARACTER SET latin1 DEFAULT NULL, 
`log` VARCHAR(3) DEFAULT 'nao', 
`cliente_id` INT(11) DEFAULT NULL, 
PRIMARY KEY (`radacctid`), 
KEY `username` (`username`), 
KEY `framedipaddress` (`framedipaddress`), 
KEY `acctsessionid` (`acctsessionid`), 
KEY `acctsessiontime` (`acctsessiontime`), 
KEY `acctuniqueid` (`acctuniqueid`), 
KEY `acctstarttime` (`acctstarttime`), 
KEY `acctstoptime` (`acctstoptime`), 
KEY `nasipaddress` (`nasipaddress`), 
KEY `cliente_id` (`cliente_id`), 
CONSTRAINT `radacct_ibfk_1` FOREIGN KEY (`cliente_id`) 
REFERENCES `radcliente` (`id`) ON DELETE no action ON UPDATE no action 
) engine=innodb auto_increment=1766573 DEFAULT charset=utf8 

The following query delay 1.5256 seconds in PHPMyAdmin, takes longer to count than to retrieve:

SELECT `t`.`id`           AS `t0_c0`, 
       `acct`.`radacctid` AS `t1_c0` 
FROM   `radcliente` `t` 
       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 
LIMIT  5000 

EXPLAINS

EXPLAIN SELECT `t`.`id`           AS `t0_c0`, 
       `acct`.`radacctid` AS `t1_c0` 
FROM   `radcliente` `t` 
       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 
LIMIT  5000 

enter image description here

EXPLAIN SELECT Count(*) 
FROM   (SELECT `t`.`id`           AS `t0_c0`, 
               `acct`.`radacctid` AS `t1_c0` 
        FROM   `radcliente` `t` 
               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) ) 
        WHERE  `acct`.`nasipaddress` = '172.21.10.17' 
        GROUP  BY t.id) sq 

enter image description here

  • What do you want to count? – ypercubeᵀᴹ Feb 20 '15 at 5:40
  • How many customers have, so 'GROUP BY t.id'. But the relationship is important as it can be filtered by the ratio, for example, 'WHERE acct.nasipaddress = 'xxx.xx.xx.xx''. I did not put in the example not to get too big and not too complicate the question. Generally it is sought by customers page 10 but has the total score that meets the filter to be used in a CGridView or any DataGrid. If the system needs to link with two examples can go smoothly. Thanks. – Anderson Scouto da Silva Feb 20 '15 at 6:01
  • Please add an example. As it is, the last query is equivalent to SELECT COUNT(*) FROM radcliente. – ypercubeᵀᴹ Feb 20 '15 at 6:03
  • But the problem is not in 'radcliente' table but in join with 'radacct'. From what I saw these four seconds are for mysql run "sorting index". I did not which example you want. If you use COUNT directly is fast but wrong account because of the group (in some cases also has having). Counting only the 'radacct' table with any filter is extramamente fast. The problem is exactly this last example I, but I can not imagine another way to make this count work. Thank You. – Anderson Scouto da Silva Feb 20 '15 at 6:26
  • I tried remove GROUP BY does not count correctly. – Anderson Scouto da Silva Feb 20 '15 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.