69392[BUGFIX] Prevent fetching of ALL records where simple sql count should works
Commit message
Review
-
Code review CR 0
-
Verified V 0
Files 1
Review log 89
-
Mariana Rozhankivska uploaded patch set 1 06/03/21, 20:38
-
core-ci voted on patch set 1 V +1 06/03/21, 20:46 Core CI is happy: https://gitlab.typo3.org/TYPO3/CI/cms/-/pipelines/12146
-
Oli Bartsch commented on patch set 1 CR -1 06/04/21, 08:52
$queryResult is initialized in the constructor. Therefore, no additional query will be performed when calling getTotalAmountOfItems(). TBH I don't see the benefit of this change. The `->count()` refers to Countable not SQL. -
Mariana Rozhankivska commented on patch set 1 06/04/21, 09:29
Reply
however, there is a HUGE difference - and it is visible once you will look, for example, in admin panel on queries performed. Let me explain. Imagine, that you have large table with, say, 10K records and you would like to show these records using QueryResultPaginator. getTotalAmountOfItems method is used to get total records count for example to calculate number of pages. And there is the magic: - if you use count($this->queryResult) - ALL records from your table are fetched (select * from your_table) and after that count() counts and returns size of your objects array. - if you call $this->queryResult->count() - (and $this->queryResult inplements countable, so count method always exists), you just perform "select count(*) from your_table" query. And this gives you big profit on large tables especially. -
Mariana Rozhankivska commented on patch set 1 06/04/21, 09:53
Unresolved
to be hones, next method "getAmountOfItemsOnCurrentPage" also may be re-developed to call ->count(), BUT items on current page are in 90% of cases fetched anyway, so, if we will replace count() woth ->count() here, it will lead to additonal query, so this is discusable. But what I proposed in current patch is really valuable. -
Oli Bartsch commented on patch set 1 06/04/21, 10:02
Reply Unresolved
Maybe I overlook something. While you are totally right with count(select *) is bad compared to (count *), AFAICS the select * was already performed since the QueryResultInterface is provided as argument on instantiating QueryResultPaginator. This usually looks like this in an controller: new QueryResultPaginator($this->someRepository->findAll()); So calling count() or ->count() on the QueryResultInterface object inside QueryResultPaginator, should not trigger another query as the query was already performed on instantiating. -
Mariana Rozhankivska commented on patch set 1 06/04/21, 10:56
Unresolved
sorry, but you are a bit wrong here. Maybe my explanation wasn't complete - sorry for that, but let me try once more. Benefit of QueryResultPaginator is NOT in fetching ALL results for every page we want to show. Call in controller of new QueryResultPaginator($this->productRepository->findAll()); NOT leads to execution of "select * from mytable" query. In view you just use {paginator.paginatedItems} to loop over, and it leads to "select * from mytable LIMIT 10" query, i.e. to limited set of records - not to all. So, there is a big advantage to use ->count() here: we save time and resources and in general it is proper approach. -
Mariana Rozhankivska commented on patch set 1 06/04/21, 11:58
but yes, maybe you are correct - no need for this change, maybe just code style. Sorry for waste of your time. -
Mariana Rozhankivska abandoned the change 06/04/21, 11:58