我对这个问题有意见 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
在其他几种情况下也会发生这种情况:总是查询计数非常慢。
谢谢你的帮助。
马科斯
暂无答案!
目前还没有任何答案,快来回答吧!