Solved! Question about laminas paginator 's cache and the way it get total number

[jobsfan]

Before I asked a question like Paginator::setCache cache paginator with filesystem adapter, how to clear it manually?

I suddenly found it is totally not work as a cache. Because that code query the mysql database everytime!! What I want is to use a cache when the cache is not expired. I am running a wrong logic these days.

@jobsfan

public function getPaginator(BasicFunc $basicFunc, $page)
{
	if (!$page) $page = 1;

	$cache = $basicFunc->fsCachePaginator('data/cache/article/design-list','paginator', 600); //this method I pasted bellow.

	Paginator::setCache($cache);

	$select = new Select('article_designs');
	$whereArr = ['valid' => 1];
	$select->where($whereArr);
	$select->order('id desc');

	$selectAdapter = new DbSelect($select, $this->tableGateway->getAdapter());
	$paginator = new Paginator($selectAdapter);
	$paginator->setCurrentPageNumber($page);
	$paginator->setPageRange(5);
	$paginator->setItemCountPerPage(12);

	return $paginator;
}

@jobsfan

public function fsCachePaginator($path, $namespace, $ttl)
{
	$this->ensurePath($path);

	$cache = $this->storageFactory->create(
		'filesystem',
		['ttl' => $ttl, 'namespace' => $namespace, 'cache_dir' => $path],
		[
			[
				'name' => 'exception_handler',
				'options' => [
					'throw_exceptions' => false,
				],
			],
			[
				'name' => 'Serializer',
			],
		],
	);

	return $cache;
}

In fact, the above code is not using a cache to avoid querying the mysql database. I just found it.

How to fixed the getPaginator to make it responsing the cache result against connecting the db and query the db when the cache is not expired.

besides, the above paginator code will make a sql query like the following(I think if I use the paginator function, I can not controll which kind of sql it compose. so is there any idea to optimise the mysql table, like create some index. because 27 seconds is too slow!!!):

# Query_time: 26.990656  Lock_time: 0.000003 Rows_sent: 1  Rows_examined: 1526448
use someuser;
SET timestamp=1703741028;
SELECT COUNT(1) AS `C` FROM (SELECT `tablename`.`id` AS `id`, `tablename`.`hz` AS `hz`, `tablename`.`zz` AS `zz`, `tablename`.`cd` AS `cd`, `tablename`.`title` AS `title` FROM `tablename` WHERE (`islock` = 1)) AS `original_select`;

Looking forward to a good solution.

I learned that it must query the totalnum whatever it has cache or not, weird logic.