performance issues when using count()

[Cliffus]

Hi!

first of all, thanks for creating this Laravel package! I'm currently integrating it for one of my web services, but I have some performance issues when using the count() method on the Query Builder.

A given lucene search query returned +/- 9000 hits, which means +/- 9000 SELECT queries are executed, just to get the count() value. I checked your code, and I noticed that these queries are needed to check the values of isSearchable(). A possible fix for this issue would be to let the developer choose to add the isSearchable() value to the lucene seach index. But I'm not sure this is possible with lucene.

What do you think? Currently, I fixed the issue by removing the isSearchable() method from my model class.

Thanks for your help! Cliff

[Cliffus]

hmm ok sorry, what I stated before was wrong. Removing the isSearchable() method doesn't fix the issue, but could be a possible solution after refactoring the code.

[Cliffus]

can you give some feedback on this issue, and why you closed this ticket?

thanks!

[nqxcode]

sorry for unexpected closing).

About implementing of indexing of models:

1. Information about all models is stored in an index.
2. In case of creation or change of search index for models isSearchable method isn't used.
3. isSearchable method is used for filtering of models in search results (after getting of result!).

The problem with a huge count of queries arises because models are looked for for each found hit.

[nqxcode]

I can fix problem with a huge count of queries only for count() but not for get() in the near future.

[Cliffus]

Hi

no problem, thanks for re-opening this issue!

My web service implements paging, with an offset of 10 items, so getting the items isn't really an issue. But the web service also adds the field 'total' to the response, so the application using this web service knows how many results there are in total.

[nqxcode]

I need to clarify one point.

When using get() or paginate() and then count(), count won't be calculated again (it will be loaded from cache).

Whether it is planned to use count() without using get() or paginate()?

[Cliffus]

currently I use both:

$total = $query->count();
$results = $query
      ->limit($limit, ($page - 1) * $limit)
      ->get();

[nqxcode]

As solution, try to swap lines in your code:

$results = $query
      ->limit($limit, ($page - 1) * $limit)
      ->get();
$total = $query->count();

it will reduce count of queries by half.

[Cliffus]

thanks, I'll try that for now!

[Cliffus]

I must say, I've optimised my search query ('m using raw queries), and now it returns better and less search results. So this kinda fixes the performance issues ;-)