[SOLVED] Performance - Edit customer user search query

[dolfiz]

Hi all,

we got 650k records on table CUSTOMER_USER and this has become the bottleneck for the performance of our system. Analyzing slow queries log we can see that almost every query that surpasses 10s is a customer search. This is due to the inefficient statement used by OTRS (there are reasons to do so, I'm not criticizing) to perform such searches.

It uses a statement like this:

Code: Select all

SELECT 
    login,
    cf_soggetto,
    last_name,
    first_name,
    denom_sogg_rappr,
    email
FROM
    customer_user
WHERE
    ((first_name LIKE '%boofoogoo%'
        OR last_name LIKE '%boofoogoo%'
        OR cf_soggetto LIKE '%boofoogoo%'
        OR phone LIKE '%boofoogoo%'))
        AND valid_id IN (1)
LIMIT 250;

We, of course, add many indexes but actually they are not used for such queries. We would like to speed up things changing a little bit that query. It can be sufficient to remove the first wildcard '%' in order to benefit from the indexes.

Where can we made this change in order to achieve our goal? I thought that /Kernel/System/CustomerUser.pm could be the proper module but I can't find - favoured by my perl ignorance - the proper point.

Any help appreciated, thanks.
Luca

[ThorstenEckel]

Hi Luca,

you were on the right path The query is build in the DB-Backend of the CustomerUser module in 'Kernel/System/CustomerUser/DB.pm' in the function 'CustomerSearch' somewhere near line 192. The query is build with some conditions and out of some configurations so it won't be that easy to add your query with just pasting it in.... some Perl knowledge would be beneficial.

[dolfiz]

Thank you! You point me in the right direction and now I'll give it a try

P.s.: nice work with Znuny4OTRS extensions; we are using some of them!

[dolfiz]

Thanks to ThorstenEckel I was able to solve the problem, and it was pretty straightforward.

As Thorsten said the logic of the customer search is within Kernel/System/CustomerUser/DB.pm and it uses CustomerUserSearchPrefix as wildcard.
It can be changed on Config.pm where we set:

Code: Select all

CustomerUserSearchFields           => [ 'first_name', 'last_name', 'cf_soggetto', 'phone' ],
CustomerUserSearchPrefix           => '',
CustomerUserSearchSuffix           => '*',
CustomerUserSearchListLimit        => 250,

I've logged the query and it was as expected: a wildcard '%' only at the end of the strings

Now we'll do some tests but according to what I've seen this change should only affect customer search, so it's perfect for us.

Hope this helps others.
Cheers,
Luca