Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Performance issues in Admin user search

Description

Pre K1 1.1.14 a search for users would not match across first and last name fields, making it impossible to find a user by searching with the conventional name. E.g. "John Smith" would not find any one.

Per this was changed so that the search entry is broken into tokens on spaces and matches are now based on an OR of finding any substring of any token in the search string in the email or name fields. Based on a code review it appears that the underlying query uses wildcards (see DbUserService.java search) and an UPPER function (see UserServiceSqlDefault.java). The upper prevents the use of an index. This means that query is potentially very expenseive when given multiple terms on a large set of users.

This should not be wide spread problem since the query is only run by the admin user.

Activity

Show:

Sam Ottenhoff October 24, 2013 at 9:18 AM

Discussed on CLE Team call

Aaron Zeckoski October 21, 2013 at 9:09 AM

Serious enough to address or should be just ignore this for now? It would probably be better to somehow about a search which is non-indexed in most any case

David Horwitz March 9, 2012 at 2:13 AM

The UPPER will have no effect in mysql appart from causing an index miss - string comparisons in Mysql are case insensitive

David Haines December 23, 2011 at 1:21 PM

I will run this idea past our DBAs.

Steve Swinsburg December 22, 2011 at 2:14 PM

only split the search string and ran the query multiple times, aggregating the results. The actual SQL remained the same so this has been around for a while. The UPPER could be removed, however that would make the search case sensitive. Perhaps the best thing to do is to combine the removal of the UPPER from the SQL with an index that already contains the fields being uppercased:
http://www.techonthenet.com/oracle/indexes.php

Won't Fix

Details

Priority

Affects versions

Components

Assignee

Reporter

Labels

Created December 22, 2011 at 11:14 AM
Updated April 25, 2018 at 3:18 PM
Resolved October 24, 2013 at 9:18 AM