cakephp3.0中的mysql分页计数查询

axzmvihb  于 2021-06-25  发布在  Mysql
关注(0)|答案(0)|浏览(221)

我对这个问题有意见 count() 在cakephp版本3.3中仍然使用分页:
我的表有600万条记录。这里涉及的领域是 name 以及 cityf . 两者在mysql中都有索引
我显示的是10和10,尽管查询速度很快 count() 分页的时间超过50秒。
如何在cakephp的3.3版本中解决这个问题。遵循以下两条sql语句和时间:
选择查询主:

SELECT 
  Rr.id AS `Rr__id`, 
  Rr.idn AS `Rr__idn`, 
  Rr.aniver AS `Rr__aniver`, 
  Rr.pessoa AS `Rr__pessoa`, 
  Rr.name AS `Rr__name`, 
  Rr.phoner AS `Rr__phoner`, 
  Rr.tipolf AS `Rr__tipolf`, 
  Rr.addressf AS `Rr__addressf`, 
  Rr.num_endf AS `Rr__num_endf`, 
  Rr.complem AS `Rr__complem`, 
  Rr.bairrof AS `Rr__bairrof`, 
  Rr.cityf AS `Rr__cityf`, 
  Rr.statef AS `Rr__statef`, 
  Rr.cepf AS `Rr__cepf`, 
  Rr.n1 AS `Rr__n1`, 
  Rr.n2 AS `Rr__n2`, 
  Rr.smerc AS `Rr__smerc`, 
  Rr.n3 AS `Rr__n3`, 
  Rr.n4 AS `Rr__n4`, 
  Rr.fone AS `Rr__fone`, 
  Rr.numero AS `Rr__numero` 
FROM 
  `MG` Rr 
WHERE 
  (
    Rr.name like 'MARCOS%' 
    AND Rr.cityf like 'BELO HORIZONTE%'
  ) 
ORDER BY 
  name asc 
LIMIT 
  10 OFFSET 0

=10毫秒
解释:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  Rr  range   NAME,CITYF,cityfbairrof,cityfaddressf,cityfbairrofaddressf,namen1n2n3n4     NAME    63  NULL    21345   Using index condition; Using where

-选择查询计数:

SELECT 
  (
    COUNT(*)
  ) AS `count` 
FROM 
  `MG` Rr 
WHERE 
  (
    Rr.name like 'MARCOS%' 
    AND Rr.cityf like 'BELO HORIZONTE%'
  )

=51.247毫秒
解释:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  Rr  range   NAME,CITYF,cityfbairrof,cityfaddressf,cityfbairrofaddressf,namen1n2n3n4     NAME    63  NULL    21345   Using index condition; Using where

在其他几种情况下也会发生这种情况:总是查询计数非常慢。
谢谢你的帮助。
马科斯

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题