Hi, My users have been experiencing issues when they try and search on content in all Tickets. The problems they experience are a very long wait 30-40 mins for the results to appear or most often the page will just hang and go to a blank page.
We have approx 45000 tickets in our database. I have done a little experimenting with the kind of queries they have been using as below: SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (Attachments_2.Content LIKE '%catalog%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ); +-------------------------+ | COUNT(DISTINCT main.id) | +-------------------------+ | 244 | +-------------------------+ 1 row in set (3 min 57.46 sec) As you can see the mysql query is appearing to take a lil under 4 mins which is ok, however it does not seem to reflect this speed using the web browser. I also ran an Explain as below: explain SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (Attachments_2.Content LIKE '%catalog%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ); +----------------+--------+-----------------------+---------+---------+- ----------------------------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------------+--------+-----------------------+---------+---------+- ----------------------------+-------+-------------+ | Attachments_2 | ALL | Attachments2 | NULL | NULL | NULL | 67372 | Using where | | Transactions_1 | eq_ref | PRIMARY,Transactions1 | PRIMARY | 4 | Attachments_2.TransactionId | 1 | Using where | | main | eq_ref | PRIMARY | PRIMARY | 4 | Transactions_1.ObjectId | 1 | Using where | +----------------+--------+-----------------------+---------+---------+- ----------------------------+-------+-------------+ I think the indeices look right but I am NO mysql guru so not entriley sure. I shall include the main RT conf options below: Loaded perl modules Perl v5.8.7 under linux Apache v1.27; Apache::Connection v1.00; Apache::Constants v1.09; Apache::DBI v0.9901; Apache::Request v1.33; Apache::Server v1.01; Apache::Session v1.6; Apache::Session::Generate::MD5 v2.1; Apache::Session::Lock::MySQL v1.00; Apache::Session::MySQL v1.01; Apache::Session::Serialize::Storable v1.00; Apache::Session::Store::DBI v1.02; Apache::Session::Store::MySQL v1.04; Apache::Status v2.03; Apache::Table v0.01; AutoLoader v5.60; B v1.09; base v2.07; Benchmark v1.07; bytes v1.02; Cache::Simple::TimedExpiry v0.23; Carp v1.04; CGI v3.10; CGI::Cookie v1.25; CGI::Util v1.5; Class::Container v0.12; Class::Data::Inheritable v0.02; Class::ReturnValue v0.53; constant v1.05; Cwd v3.05; Data::Dumper v2.121_04; DBD::mysql v3.0002_1; DBI v1.48; DBIx::SearchBuilder v1.32; DBIx::SearchBuilder::Unique v0.01; Devel::StackTrace v1.11; Devel::StackTraceFrame v0.6; Digest::base v1.00; Digest::MD5 v2.33; DynaLoader v1.05; Encode v2.10; Encode::Alias v2.03; Encode::Config v2.00; Encode::Encoding v2.02; Errno v1.0901; Exception::Class v1.21; Exception::Class::Base v1.2; Exporter v5.58; Exporter::Heavy v5.58; Fcntl v1.05; fields v2.03; File::Basename v2.73; File::Glob v1.04; File::Path v1.07; File::Spec v3.05; File::Spec::Unix v1.5; File::Temp v0.16; FileHandle v2.01; HTML::Entities v1.29; HTML::Mason v1.3101; HTML::Mason::ApacheHandler v1.69; HTML::Mason::Exception v1.1; HTML::Mason::Exception::Abort v1.1; HTML::Mason::Exception::Compilation v1.1; HTML::Mason::Exception::Compilation::IncompatibleCompiler v1.1; HTML::Mason::Exception::Compiler v1.1; HTML::Mason::Exception::Decline v1.1; HTML::Mason::Exception::Params v1.1; HTML::Mason::Exception::Syntax v1.1; HTML::Mason::Exception::System v1.1; HTML::Mason::Exception::TopLevelNotFound v1.1; HTML::Mason::Exception::VirtualMethod v1.1; HTML::Mason::Exceptions v1.43; HTML::Parser v3.45; HTML::Scrubber v0.08; I18N::LangTags v0.35; I18N::LangTags::Detect v1.03; integer v1.00; IO v1.21; IO::File v1.11; IO::Handle v1.24; IO::InnerFile v2.110; IO::Lines v2.110; IO::Scalar v2.110; IO::ScalarArray v2.110; IO::Seekable v1.09; IO::Wrap v2.110; IO::WrapTie v2.110; IPC::Open2 v1.01; IPC::Open3 v1.0106; lib v0.5565; List::Util v1.14; locale v1.00; Locale::Maketext v1.09; Locale::Maketext::Fuzzy v0.02; Locale::Maketext::Lexicon v0.49; Locale::Maketext::Lexicon::Gettext v0.14; Log::Dispatch v2.11; Log::Dispatch::Base v1.09; Log::Dispatch::File v1.22; Log::Dispatch::Output v1.26; Log::Dispatch::Screen v1.17; Log::Dispatch::Syslog v1.18; Mail::Address v1.67; Mail::Field v1.67; Mail::Field::AddrList v1.67; Mail::Header v1.67; Mail::Internet v1.67; MIME::Base64 v3.05; MIME::Body v5.417; MIME::Decoder v5.417; MIME::Decoder::NBit v5.417; MIME::Entity v5.417; MIME::Field::ContDisp v5.417; MIME::Field::ConTraEnc v5.417; MIME::Field::ContType v5.417; MIME::Field::ParamVal v5.417; MIME::Head v5.417; MIME::Parser v5.417; MIME::QuotedPrint v3.03; MIME::Tools v5.417; MIME::Words v5.417; mod_perl v1.29; Module::Versions::Report v1.02; overload v1.03; Params::Validate v0.78; POSIX v1.08; re v0.04; Regexp::Common v2.120; Regexp::Common::_support v2.101; Regexp::Common::balanced v2.101; Regexp::Common::CC v2.100; Regexp::Common::comment v2.116; Regexp::Common::delimited v2.104; Regexp::Common::lingua v2.105; Regexp::Common::list v2.103; Regexp::Common::net v2.105; Regexp::Common::number v2.108; Regexp::Common::profanity v2.104; Regexp::Common::SEN v2.102; Regexp::Common::URI v2.108; Regexp::Common::URI::fax v2.100; Regexp::Common::URI::file v2.100; Regexp::Common::URI::ftp v2.101; Regexp::Common::URI::gopher v2.100; Regexp::Common::URI::http v2.101; Regexp::Common::URI::news v2.100; Regexp::Common::URI::pop v2.100; Regexp::Common::URI::prospero v2.100; Regexp::Common::URI::RFC1035 v2.100; Regexp::Common::URI::RFC1738 v2.104; Regexp::Common::URI::RFC1808 v2.100; Regexp::Common::URI::RFC2384 v2.102; Regexp::Common::URI::RFC2396 v2.100; Regexp::Common::URI::RFC2806 v2.100; Regexp::Common::URI::tel v2.100; Regexp::Common::URI::telnet v2.100; Regexp::Common::URI::tv v2.100; Regexp::Common::URI::wais v2.100; Regexp::Common::whitespace v2.103; Regexp::Common::zip v2.112; RT v3.4.4; RT::Interface::Email v1.02; Scalar::Util v1.14; SelectSaver v1.01; Socket v1.77; Storable v2.13; strict v1.03; Symbol v1.06; Sys::Hostname v1.11; Sys::Syslog v0.06; Text::Autoformat v1.13; Text::Quoted v1.8; Text::Reform v1.11; Text::Tabs v98.112801; Text::Template v1.44; Text::Wrapper v1.000; Time::HiRes v1.66; Time::JulianDay v2003.1125; Time::Local v1.11; Time::ParseDate v2003.1126; Time::Timezone v2003.0211; Tree::Simple v1.15; URI v1.35; URI::Escape v3.28; URI::URL v5.03; URI::WithBase v2.19; utf8 v1.05; vars v1.01; warnings v1.03; warnings::register v1.00; XSLoader v0.02; RT Variables RT::AmbiguousDayInPast 1 RT::BasePath /usr/local/rt3 RT::BinPath /usr/local/rt3/bin RT::CORE_CONFIG_FILE /usr/local/rt3/etc/RT_Config.pm RT::CommentAddress X RT::CorrespondAddress X RT::DatabaseHost localhost RT::DatabaseName X RT::DatabasePassword Password not printed RT::DatabaseRTHost localhost RT::DatabaseType mysql RT::DatabaseUser X RT::DateDayBeforeMonth 1 RT::DefaultSearchResultFormat '<B><A HREF="/Ticket/Display.html?id=__id__">__id__</a></B>/TITLE:#', '<B><A HREF="/Ticket/Display.html?id=__id__">__Subject__</a></B>/TITLE:Subject' , '<small><A HREF="/Ticket/Display.html?id=__id__"></a>__CustomField.{ClientID}__</sm all>/TITLE:ClientID', Status, QueueName, OwnerName, '__NEWLINE__', '', '<small>__Requestors__</small>', '<small><A HREF="/Ticket/Display.html?id=__id__"></a>__CustomField.{AccountsBillabl e}__</small>/TITLE:AccountsBillable', '<small>__CreatedRelative__</small>', '<small>__ToldRelative__</small>', '<small>__LastUpdatedRelative__</small>' RT::EmailOutputEncoding utf-8 RT::EtcPath /usr/local/rt3/etc RT::FriendlyFromLineFormat "%s via RT" <%s> RT::FriendlyToLineFormat X RT::LocalEtcPath /usr/local/rt3/local/etc RT::LocalLexiconPath /usr/local/rt3/local/po RT::LocalPath /usr/local/rt3/local RT::LogDir /usr/local/rt3/var/log RT::LogToFile info RT::LogToFileNamed rt.log RT::LogToScreen error RT::LogToSyslog debug RT::LogoURL /NoAuth/images/rt.jpg RT::LoopsToRTOwner 1 RT::MailCommand sendmailpipe RT::MasonComponentRoot /usr/local/rt3/share/html RT::MasonDataDir /usr/local/rt3/var/mason_data RT::MasonLocalComponentRoot /usr/local/rt3/local/html RT::MasonSessionDir /usr/local/rt3/var/session_data RT::MaxAttachmentSize 10000000 RT::MaxInlineBody 13456 RT::MessageBoxWidth 72 RT::MessageBoxWrap HARD RT::MinimumPasswordLength 5 RT::MyRequestsLength 20 RT::MyTicketsLength 20 RT::Organization X RT::OwnerEmail X RT::RTAddressRegexp [EMAIL PROTECTED] RT::RecordOutgoingEmail 1 RT::RedistributeAutoGeneratedMessages 1 RT::SITE_CONFIG_FILE /usr/local/rt3/etc/RT_SiteConfig.pm RT::SendmailArguments -oi -t RT::SendmailPath /usr/sbin/sendmail RT::Timezone Europe/London RT::UseFriendlyFromLine 1 RT::VERSION 3.4.4 RT::VarPath /usr/local/rt3/var RT::WebBaseURL http://rt.foreshore.net RT::WebFlushDbCacheEveryRequest 1 RT::WebImagesURL /NoAuth/images/ RT::WebURL http://rt.foreshore.net/ RT::rtname foreshore.net Perl configuration Summary of my perl5 (revision 5 version 8 subversion 7) configuration: Platform: osname=linux, osvers=2.4.21-32.0.1.elsmp, archname=i686-linux uname='linux linuxserv02.foreshore.net 2.4.21-32.0.1.elsmp #1 smp tue may 17 17:52:23 edt 2005 i686 i686 i386 gnulinux ' config_args='' hint=previous, useposix=true, d_sigaction=define usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=undef use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='gcc', ccflags ='-fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm', optimize='-O2', cppflags='-fno-strict-aliasing -pipe -I/usr/local/include -I/usr/include/gdbm -fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm' ccversion='', gccversion='3.2.3 20030502 (Red Hat Linux 3.2.3-53)', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=4, prototype=define Linker and Libraries: ld='gcc', ldflags =' -L/usr/local/lib' libpth=/usr/local/lib /lib /usr/lib libs=-lnsl -lgdbm -ldb -ldl -lm -lcrypt -lutil -lc perllibs=-lnsl -ldl -lm -lcrypt -lutil -lc libc=/lib/libc-2.3.2.so, so=so, useshrplib=true, libperl=libperl.so gnulibc_version='2.3.2' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E -Wl,-rpath,/usr/local/lib/perl5/5.8.7/i686-linux/CORE' cccdlflags='-fpic', lddlflags='-shared -L/usr/local/lib' I have also tried to add the Content-Type matches 'text/plain' to the search but this seems to not cause much benefit as below: SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType = 'RT::Ticket')AND(Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (Attachments_2.Content LIKE '%catalog%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) AND ( (Attachments_2.ContentType LIKE '%text/plain%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ); +-------------------------+ | COUNT(DISTINCT main.id) | +-------------------------+ | 159 | +-------------------------+ 1 row in set (2 min 58.34 sec) mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType = 'RT::Ticket')AND(Transactions_1.ObjectType = 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( ( (Attachments_2.Content LIKE '%catalog%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) AND ( (Attachments_2.ContentType LIKE '%text/plain%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ); +----------------+--------+-----------------------+---------+---------+- ----------------------------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------------+--------+-----------------------+---------+---------+- ----------------------------+-------+-------------+ | Attachments_2 | ALL | Attachments2 | NULL | NULL | NULL | 67382 | Using where | | Transactions_1 | eq_ref | PRIMARY,Transactions1 | PRIMARY | 4 | Attachments_2.TransactionId | 1 | Using where | | main | eq_ref | PRIMARY | PRIMARY | 4 | Transactions_1.ObjectId | 1 | Using where | +----------------+--------+-----------------------+---------+---------+- ----------------------------+-------+-------------+ 3 rows in set (0.03 sec) If anyone has any suggestions on how to speed this up (i.e. Indexs to create, or perl modules to update) please let me know I have checked on the forums but couldn't seem to find anything relevant. Regards David Wells MCSE, RHCE & CCNP Senior Network Engineer Foreshore Limited Direct Line: +44 1534 752316 Facsimile: +44 1534 752301 Email@ [EMAIL PROTECTED] http://www.foreshore.net http://privacyprofessional.com ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com We're hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html