Project

General

Profile

Actions

Feature #17889

open

Searches should be twice faster

Added by Olivier Houdas over 9 years ago. Updated over 9 years ago.

Status:
New
Priority:
Normal
Assignee:
-
Category:
Search engine
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:

Description

When we run a search, we can see in the debug log that for each category to look into ( issues, forums, wiki...) there are 2 queries which are almost the same, and take the same time. One is for counting results, the other one is for getting the first 11 lines.
This is discussed in http://www.redmine.org/boards/1/topics/41475 for example.

In /lib/plugins/acts_as_searchable.rb, line 126 and 128, we have (I think) the origin of those 2 queries:

l 126: results_count = scope.count
l 128: scope_with_limit = scope.limit(options[:limit])

Wouldn't it be possible to include a column like

"total=COUNT(*) OVER()"
(MSSQL or Oracle syntax) to get that results_count in only one query? I tested the query, and it takes the same time with or without the total column, even if on a large table (7000ms of query execution).

I mean, instead of having 2 queries:

[1m[35m (7805.9ms)[0m  EXEC sp_executesql N'SELECT COUNT(DISTINCT [issues].[id]) FROM ... ))))))

and

[1m[36mSQL (7592.5ms)[0m  [1mEXEC sp_executesql N'SELECT TOP (11) [issues].id FROM ... ))))))  GROUP BY [issues].id, issues.id ORDER BY issues.id DESC'[0m

We would have only one query:

[1m[36mSQL (7592.5ms)[0m  [1mEXEC sp_executesql N'SELECT TOP (11) [issues].id, total=COUNT(*) OVER() FROM ... ))))))  GROUP BY [issues].id, issues.id ORDER BY issues.id DESC'[0m

And that would make searches twice faster.


Related issues

Related to Redmine - Feature #18631: Better search results paginationClosedJean-Philippe Lang

Actions
Actions

Also available in: Atom PDF