| # |
Aug 22nd 2019, 11:05 |
 alexdd55976 |
> OR |
| # |
Aug 22nd 2019, 11:04 |
conehead |
The foreign_key might contain ids or uuids |
| # |
Aug 22nd 2019, 11:03 |
conehead |
Urgh...I got an idea |
| # |
Aug 22nd 2019, 11:03 |
conehead |
``` SELECT ( COUNT(*) ) AS `count` FROM documents Documents LEFT JOIN statuses Status ON ( Documents.id = (Status.foreign_key) ) ``` |
| # |
Aug 22nd 2019, 11:00 |
alexdd55976 |
so whta the current query? |
| # |
Aug 22nd 2019, 11:00 |
conehead |
@alexdd55976 yes I removed it and still takes 8 seconds. |
| # |
Aug 22nd 2019, 11:00 |
conehead |
@steinkel I can check but it is not a query I perform myself. |
| # |
Aug 22nd 2019, 11:00 |
alexdd55976 |
`LEFT JOIN statuses Status ON ( Status.model = 'Documents'` thats slows things down |
| # |
Aug 22nd 2019, 10:59 |
steinkel |
`->counter` would allow you to tweak the query for count() |
| # |
Aug 22nd 2019, 10:58 |
steinkel |
@conehead check this https://book.cakephp.org/3.0/en/orm/query-builder.html#returning-the-total-count-of-records |
| # |
Aug 22nd 2019, 10:51 |
conehead |
When I remove that additional condition, SQL time is reduced from 22 seconds to 8 seconds for this query :S |
| # |
Aug 22nd 2019, 10:51 |
conehead |
Oh and yes the count is actually affected as there is a condition for the model field |
| # |
Aug 22nd 2019, 10:50 |
conehead |
Yes I did. The query itself is working fine and fast. When passing the query to pagination, shit hits the fan |
| # |
Aug 22nd 2019, 10:50 |
steinkel |
^ count query I mean |
| # |
Aug 22nd 2019, 10:49 |
steinkel |
well, you can define your pagination query |
| # |
Aug 22nd 2019, 10:49 |
neon1024 |
I have a feeling that might be the Paginator doing the count, as the query passed to it has a join |
| # |
Aug 22nd 2019, 10:49 |
conehead |
This is what cake does when paginating |
| # |
Aug 22nd 2019, 10:48 |
steinkel |
@conehead why using left join on count? it's not going to affect the number of rows... try `SELECT COUNT(id) AS `documentsCount` FROM documents Documents` |
| # |
Aug 22nd 2019, 10:45 |
spriz |
maybe time to go to offset based pagination :slightly_smiling_face: |
| # |
Aug 22nd 2019, 10:45 |
spriz |
`count(*)` is tought one |
| # |
Aug 22nd 2019, 10:44 |
conehead |
Yes, model and foreign key are combined a unique key now. And it boosted performance for the simple queries...but not for pagination ;D |
| # |
Aug 22nd 2019, 10:43 |
neon1024 |
Does `Status.model` have an index? Although I’m guessing now. We’ve moved beyond my level of expertise :P |
| # |
Aug 22nd 2019, 10:42 |
conehead |
``` SELECT ( COUNT(*) ) AS `count` FROM documents Documents LEFT JOIN statuses Status ON ( Status.model = 'Documents' AND Documents.id = (Status.foreign_key) ) ``` Well I will have a look |
| # |
Aug 22nd 2019, 10:41 |
conehead |
It is a quiet simple one... |
| # |
Aug 22nd 2019, 10:40 |
neon1024 |
Perhaps it’s time to profile the queyr |
| # |
Aug 22nd 2019, 10:39 |
conehead |
Confusing....looks like a simple count SQL takes 26 seconds |
| # |
Aug 22nd 2019, 10:36 |
conehead |
Wanna paginate with datatables |
| # |
Aug 22nd 2019, 10:36 |
conehead |
No I need more later. Just for testing now 1 item |
| # |
Aug 22nd 2019, 10:35 |
neon1024 |
Or, are you doing like a paginated view? Where you view a single document per page? |
| # |
Aug 22nd 2019, 10:35 |
neon1024 |
That strikes me as an odd query to do. If you want a single document, would you not find it by id? Or is this an example where you’ve reduced it to 1 item to test? |
| # |
Aug 22nd 2019, 10:29 |
conehead |
@neon1024 Yes, pretty much lots of rows. But I only need to get one `Document` with it's `Status`. Currently there are roughly 6k documents and 8k statuses. This here results in a timeout: ``` $this->paginate = [ 'limit' => 1, 'contain' => [ 'Status', ], ]; $pag = $this->paginate($this->Documents->find()); ``` |
| # |
Aug 22nd 2019, 10:24 |
neon1024 |
@ndm Nor me :man-shrugging: It might just be my brain, or my bad code. I’ve historically always had issues with this part of the framework |
| # |
Aug 22nd 2019, 10:24 |
neon1024 |
Perhaps that combined with the joins is slowing things down? Perhaps it’s worth experimenting with an offset paginator |
| # |
Aug 22nd 2019, 10:24 |
ndm |
@neon1024 I don't really see how that could possibly happen, unless maybe the request object is modified somewhere. |
| # |
Aug 22nd 2019, 10:23 |
neon1024 |
@conehead Do you have lots of rows? Cake uses limit pagination by default, so it’ll load the entire result set and sub-set for the page |
| # |
Aug 22nd 2019, 10:22 |
conehead |
Hm neon my problem is actually not really solved with the missing index. When just executing the query the result comes really fast. When passing this query to a paginator, I get timeouts again. The (hasOne) relations that slow everything down have a condition |
| # |
Aug 22nd 2019, 10:20 |
neon1024 |
Needs work imho |
| # |
Aug 22nd 2019, 10:19 |
neon1024 |
RIP CSRF Middleware |
| # |
Aug 22nd 2019, 10:19 |
neon1024 |
Lol, even the whitelist callback doesn’t work |
| # |
Aug 22nd 2019, 10:17 |
neon1024 |
Yep |
| # |
Aug 22nd 2019, 10:16 |
ndm |
You mean the paramter in the request object is empty? |