Hi all, As Martin proposed I want to post my first contribution here. Feedback is welcomed. Do you think this is useful ? Any chance to include this or similar into a future release ? Gruss -paul Problem description: In OTRS 1.1.2 with mysql, if "customer_user" table has a lot of entries the "phone view" customer search gets slow. This is mainly due to the fact that mysql only uses one index per query and OTRS porduces a "where clause" with ORed Like conditions. In my set up to search through 750 000 entries on 4 searchable fields (phone, email, last_name, customer_id) it took up to 8 seconds to produce a result. Idea: Have one query per search field and define one index per the searchable field in mysql. Add a switch in Config.pm to switch this on Result: It really flies again. In my setup answer time is < 0.01 seconds. Counter indication: If you enter a criteria starting with * or % the query might take (searchable fileds ) * longer than the original otrs code. The mysql indexes are not usable in those cases. What you need to do (5 things you shall be prepared to do): 1) Add some fields to the customer_user table mysql> show columns from customer_user +-------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------------------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | login | varchar(100) | | UNI | | | | email | varchar(150) | | MUL | | | | customer_id | varchar(200) | | MUL | | | | pw | varchar(50) | | | | | | salutation | varchar(50) | YES | | NULL | | | first_name | varchar(100) | | | | | | last_name | varchar(100) | | MUL | | | | valid_id | smallint(6) | | | 0 | | | comment | varchar(250) | | | | | | create_time | datetime | | | 0000-00-00 00:00:00 | | | create_by | int(11) | | | 0 | | | change_time | datetime | | | 0000-00-00 00:00:00 | | | change_by | int(11) | | | 0 | | | phone | varchar(255) | YES | MUL | NULL | | | address | varchar(255) | YES | | NULL | | | info_source | varchar(10) | YES | | otrs | | | line_card | varchar(50) | YES | | NULL | | | icms_id | varchar(50) | YES | | NULL | | +-------------+--------------+------+-----+---------------------+----------------+ 2) create indexes on the searchable fileds +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---- -----+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Com ment | +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---- -----+ | customer_user | 0 | PRIMARY | 1 | id | A | 747536 | NULL | NULL | | | customer_user | 0 | login | 1 | login | A | 0 | NULL | NULL | | | customer_user | 1 | phone | 1 | phone | A | 747536 | NULL | NULL | | | customer_user | 1 | customer_id | 1 | customer_id | A | 186884 | NULL | NULL | | | customer_user | 1 | last_name | 1 | last_name | A | 93442 | NULL | NULL | | | customer_user | 1 | email | 1 | email | A | 57502 | NULL | NULL | | +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+---- -----+ 6 rows in set (0.00 sec) 3) Source changes 3) A) DB.pm in ../Kernel/System/CustomerUser after # -- # max shown user a search list # -- $Self->{UserSearchListLimit} = 250; # -- # config options # -- add this # ept changes begin $Self->{EPTCustomerUserSearch} = $Self->{ConfigObject}->Get('CustomerUser')->{Params}->{EPTCustomerUserSearch}; # ept changes end and after this (around line 170) $SQL .= " FROM " . " $Self->{CustomerTable} ". " WHERE "; add this # ept changes 2003.07.14 dinparh begin if ($Param{Search} && $Self->{EPTCustomerUserSearch} ){ $Param{Search} =~ s/\*/%/g; foreach (@{$Self->{ConfigObject}->Get('CustomerUser')->{CustomerUserSearchFields}}) { my $ept_SQL = $SQL. " $_ LIKE '".$Self->{DBObject}->Quote($Param{Search})."' "; #get data from db $Self->{DBObject}->Prepare(SQL => $ept_SQL, Limit => $Self->{UserSearchListLimit}); while (my @Row = $Self->{DBObject}->FetchrowArray()) { foreach (1..8) { if ($Row[$_]) { $Users{$Row[0]} .= $Row[$_].' '; } } $Users{$Row[0]} =~ s/^(.*\s)(.+?\@.+?\..+?)(\s|)$/"$1" <$2>/; } } return %Users; } # ept changes 2003.07.14 dinparh end 3) B) in ../Kernel/Config.pm after: $Self->{CustomerUser} = { Module => 'Kernel::System::CustomerUser::DB', Params => { Table => 'customer_user', add this: EPTCustomerUserSearch => 1, 4) You might want to adjust $Self->{UserSearchListLimit} in config.pm ---------------------------------------------------------------------------------------------------------------------- Electronic information is not secure, therefore the Entreprise des Postes et Telecommunications accepts no responsibility whatsoever for the contents of this message, which is intended for the addressee only. Its contents and any attached files are strictly confidential. If you have received this message in error, please telephone the following number 4765-1.