spacedog4
764
24
Laravel

Dealing with huge tables and pagination

Posted 4 months ago by spacedog4

I have a table with more then 16000 records (see the update), I need to show it on a list and paginate, but even with simplePagination it takes 15s to query it, is there a better way to paginate it?

Important thing is that I cant select the first 100 and page 2 the next 100 cause I have filters

I'm using a google compute engine and sql with 1vCPU and 3,7GB ram

Update

I forgot to mention that I'm making some relationships, each record can have 6 to 9 relations, so I might be selectiong 100.000 to 200.000 records

My SQL

Here is my sql, the tables are in portuguese so it might be difficult to read

I'm dealing with 4 tables

pedidoproducao has 17567 records

pedidoproducaoetapas has 19360 records

pedidoproducaoocorrencia has just 1 record

pedidoproducaoderivado has 17559 records

produtos has 2301 records

I don't know why the laravel don't built it on sql but I'm making some relationships with with that are not in sql, like etapa_producao and ocorrencia

$query->with('etapas');
$query->with(['etapas.etapa_producao' => function ($q) {
    $q->select('codigoempresa', 'codigoetapasproducao', 'etapa');
}]);
$query->with('etapas.ocorrencias.ocorrencia');
SELECT `pedidoproducao`.*,
  `pedidoproducao`.`hora` AS `hora_previsao`,
  `pedidoproducaoetapas`.`previsao`,
  DATE_ADD(pedidoproducao.data, INTERVAL SUM(pedidoproducaoetapas.previsao) DAY) AS data_previsao,

  (SELECT SUM(previsao) AS previsao
   FROM `pedidoproducaoetapas`
   WHERE `pedidoproducao`.`codigoempresa` = `pedidoproducaoetapas`.`codigoempresa`
         AND `pedidoproducao`.`codigofilial` = `pedidoproducaoetapas`.`codigofilial`
         AND `pedidoproducao`.`codigopedidoproducao` = `pedidoproducaoetapas`.`codigopedidoproducao`) AS `previsao`,

  (SELECT count(*)
   FROM `pedidoproducaoocorrencia`
   WHERE `pedidoproducao`.`codigoempresa` = `pedidoproducaoocorrencia`.`codigoempresa`
         AND `pedidoproducao`.`codigofilial` = `pedidoproducaoocorrencia`.`codigofilial`
         AND `pedidoproducao`.`codigopedidoproducao` = `pedidoproducaoocorrencia`.`codigopedidoproducao`) AS `ocorrencia`
FROM `pedidoproducao`
  INNER JOIN `pedidoproducaoderivado` ON (`pedidoproducaoderivado`.`codigoempresa` = `pedidoproducao`.`codigoempresa`
                                          AND `pedidoproducaoderivado`.`codigofilial` = `pedidoproducao`.`codigofilial`
                                          AND `pedidoproducaoderivado`.`codigopedidoproducao` = `pedidoproducao`.`codigopedidoproducao`)
  INNER JOIN `produtos` ON (`produtos`.`codigoempresa` = `pedidoproducaoderivado`.`codigoempresa`
                            AND `produtos`.`codigopro` = `pedidoproducaoderivado`.`codigopro`)
  LEFT JOIN `pedidoproducaoetapas` ON (`pedidoproducao`.`codigoempresa` = `pedidoproducaoetapas`.`codigoempresa`
                                       AND `pedidoproducao`.`codigopedidoproducao` = `pedidoproducaoetapas`.`codigopedidoproducao`)
WHERE EXISTS
      (SELECT *
       FROM `pedidoproducaoetapas`
       WHERE `pedidoproducao`.`codigoempresa` = `pedidoproducaoetapas`.`codigoempresa`
             AND `pedidoproducao`.`codigofilial` = `pedidoproducaoetapas`.`codigofilial`
             AND `pedidoproducao`.`codigopedidoproducao` = `pedidoproducaoetapas`.`codigopedidoproducao`)
      AND `pedidoproducao`.`codigoempresa` = ?
      AND `pedidoproducao`.`codigofilial` = ?
      AND `pedidoproducao`.`status` = ?
GROUP BY `pedidoproducao`.`codigopedidoproducao`
ORDER BY `pedidoproducao`.`updated_at` DESC,
  `pedidoproducao`.`codigopedidoproducao` ASC

Notice

If is there a way I can do this with cache, cause this page is an history of items that was completed, but I don't know how cache works, I can cache the query and update the cache every time a new item is completed?

Please sign in or create an account to participate in this conversation.