Repeated conditionals resulting in slow queries

[kieran-s]

I've come across an issue, which is causing fastpaginate to operate about 3x slower than normal paginate.
I believe this is the same thing as mentioned in issue #41

From what I can see (I may be wrong here), when using fastpaginate, 3 queries are executed:

• A pagination query (SELECT count(*) as aggregate...) to populate page numbers
• A limit/offset/range query (SELECT id FROM table WHERE <your ORM where conditions>) to get the IDs to select
• A results query (SELECT <selected_columns> FROM table WHERE <your ORM conditions> AND id IN ( <list_from_last_query>)

I have a complex where query run, which is executed by fastpaginate (SELECT id FROM ...) to get the ID list, and then it does the results query (SELECT <rows> FROM ...) and adds the WHERE id IN (...)), however, it leaves the rest of the complex where query there, so it's searching through all the WHEREs etc as -well- as the ID in ...
(for those curious, its complex due to applying a multi-option filter to results)

This means that FastPaginate takes about 30 seconds (between the pagination query, ID list query and results query) to execute where the inbuilt paginator (with just the pagination query and results query) takes about 10 seconds.

I've done some light testing in raw SQL using the final results query that fastpaginate executed (grabbed from telescope). If I run the query in my SQL client, it takes about 22 seconds (which matches telescopes execution time display)... if I remove my complex where query and just leave it as the ID where (SELECT <columns> FROM table WHERE id IN (...)), it takes 50-100ms.

Final results query as executed by fastpaginate

Same query as above, but with all WHERE clauses removed except WHERE id IN (...)

Sure, I can probably do more to optimise the database and/or queries... But from what I've seen (admittedly, I'm not super skilled in proper deep debugging of database performance), it would seem like having fastPaginate rewrite the last query to only use the WHERE id IN (...) and none of the other WHERE etc clauses could potentially have some performance assists in certain cases. Not sure how hard this would be (or if it's even possible), but would be amazing if it could be done.

Telescope Query Stats - FastPaginate

Telescope Query Stats - normal Paginate

These are the queries in question that are being executed by the above.

Executed by fastPaginate:

select `logs`.`id` from `logs` where ( `type` = 'admin' or ( `type` = 'inventory' and `action` = 'removeItem' ) or ( `type` = 'inventory' and `action` = 'addItem' ) ) order by `time` desc limit 20 offset 0;

select * from `logs` where ( `type` = 'admin' or ( `type` = 'inventory' and `action` = 'removeItem') or ( `type` = 'inventory' and `action` = 'addItem' ) ) and `logs`.`id` in (
    10411771,
    10411700,
    10411683,
    10411671,
    10411657,
    10411651,
    10411642,
    10411618,
    10411617,
    10411613,
    10411602,
    10411605,
    10411601,
    10411603,
    10411604,
    10411600,
    10411606,
    10411597,
    10411599,
    10411598
  ) order by `time` desc limit 21 offset 0

Changing the second query to

select * from `logs` where  `logs`.`id` in (
    10411771,
    10411700,
    10411683,
    10411671,
    10411657,
    10411651,
    10411642,
    10411618,
    10411617,
    10411613,
    10411602,
    10411605,
    10411601,
    10411603,
    10411604,
    10411600,
    10411606,
    10411597,
    10411599,
    10411598
  ) order by `time` desc limit 21 offset 0

cuts off about 20 seconds or so in execution.

[kieran-s]

I'm not very experienced with the laravel base query builder, but from playing around in tinkerwell, could you possibly just clear the wheres from the builder for the final query and then add the IDs whereIn? Would that work?

$query = $this->getQuery();
$query->bindings["where"] = [];
$query->wheres = [];
$query->whereIn($key, $idList);

[aarondfrancis]

Hmm this is interesting. And I'm very surprised that the database doesn't choose to filter by the IN condition.

What database + version are you using?