| # |
Aug 22nd 2019, 11:10 |
 alexdd55976 |
serious question. |
| # |
Aug 22nd 2019, 11:10 |
alexdd55976 |
you sure that a combined index is the way to go there? |
| # |
Aug 22nd 2019, 11:09 |
alexdd55976 |
was thinking about uuids .. but seems that uuids would make more sense, if used all over the place and not mixed up |
| # |
Aug 22nd 2019, 11:08 |
alexdd55976 |
i have a similar approach planed in my new project |
| # |
Aug 22nd 2019, 11:07 |
conehead |
it is a Unique Index combined with `model` and `foreign_key` |
| # |
Aug 22nd 2019, 11:07 |
alexdd55976 |
that explains longer response time |
| # |
Aug 22nd 2019, 11:06 |
conehead |
Status can be linked to different entities. And some entities have a uuid and some have an id |
| # |
Aug 22nd 2019, 11:06 |
alexdd55976 |
so its a text fulltext index? |
| # |
Aug 22nd 2019, 11:06 |
conehead |
No, both CHAR(36), but sometimes the CHAR just stores the id |
| # |
Aug 22nd 2019, 11:05 |
alexdd55976 |
how can you have both? its a different datatype? |
| # |
Aug 22nd 2019, 11:05 |
conehead |
Which imho should not be a problem |
| # |
Aug 22nd 2019, 11:05 |
alexdd55976 |
thats a tough one |
| # |
Aug 22nd 2019, 11:05 |
conehead |
yes |
| # |
Aug 22nd 2019, 11:05 |
alexdd55976 |
? |
| # |
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 |