[Koha-bugs] [Bug 33554] searching borrowers is a lot slower if there's searchable extended attributes.

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33554

--- Comment #12 from Kyle M Hall <kyle at bywatersolutions.com> ---
I feel like we could greatly simplify and speed up patron searching by simply
have a new table column or columns that exist solely to contain searchable
content. We could even store this in JSON format and make use of JSON functions
at a later date if possible.

Here are the default patron search fields:
firstname,middle_name,surname,othernames,cardnumber,userid

Let's say I have a patron:
Joe Gerald Patronson, esquire ( 123456789 ), jpatronson

Upon editing, we could store the following:
{
  firstname: "Joe",
  middle_name: "Gerald",
  surname: "Patronson",
  othernames: "esquire",
  cardnumber: "123456789",
  userid: "jpatronson",
}

Now, if we need to search the entire field we can do a
LIKE ': "%<VALUE>%\n'
for contains and
LIKE ': "<VALUE>%\n'
for begins with

If we want to search for patrons with a firstname starting with J and surname
containing son it would like like
WHERE newcolumn LIKE 'firstname: "J%"\n' AND newcolumn LIKE 'surname:
"%son%"\n'

If we do this, we can have a single column to search and index.
We can also add extended attributes to this by attribute code, for example:
{
  firstname: "Joe",
  middle_name: "Gerald",
  surname: "Patronson",
  othernames: "esquire",
  cardnumber: "123456789",
  userid: "jpatronson",
  ATTR: "value1",
  ATTR: "value2",
}

Right now we cannot search on specific attributes, but that could easily be
added using this system. This allows us to search all needed patron data
without the need for complex joins.

I think TINYTEXT might be a bit too small, but we could definitely be fine with
TEXT and we can specify the length of the index as a reasonable number ( say
500 characters or so ).

Thoughts?

-- 
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.