Hello, Is there a way to solve improve this query? If multiple users search for the below this locks the database and trac is not responding until these queries are done.
What would I have to do to either get this query to not run in temporary table, or use the temporary table in memory/cache? How can I find out if its using memory or disk? What else do I need to look at? Using mysql. # Time: 100415 8:50:47 # u...@host: trac[trac] @ [xx.xx.xx.xx] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 SELECT COUNT(*) FROM (SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.version AS version,t.owner AS owner,t.priority AS priority,t.component AS component,t.resolution AS resolution,t.keywords AS keywords,t.changetime AS changetime,t.status AS status,t.time AS time,priority.value AS priority_value,contract_number.value AS contract_number,agency_number.value AS agency_number FROM ticket AS t LEFT OUTER JOIN ticket_custom AS contract_number ON (id=contract_number.ticket AND contract_number.name='contract_number') LEFT OUTER JOIN ticket_custom AS agency_number ON (id=agency_number.ticket AND agency_number.name='agency_number') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE COALESCE(contract_number.value,'') LIKE '%2473297%' ESCAPE '/' ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id) AS foo; # Time: 100415 8:50:56 # u...@host: trac[trac] @ [xx.xx.xx.xx] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 334156 SELECT t.id AS id,t.summary AS summary,t.milestone AS milestone,t.version AS version,t.owner AS owner,t.priority AS priority,t.component AS component,t.resolution AS resolution,t.keywords AS keywords,t.changetime AS changetime,t.status AS status,t.time AS time,priority.value AS priority_value,contract_number.value AS contract_number,agency_number.value AS agency_number FROM ticket AS t LEFT OUTER JOIN ticket_custom AS contract_number ON (id=contract_number.ticket AND contract_number.name='contract_number') LEFT OUTER JOIN ticket_custom AS agency_number ON (id=agency_number.ticket AND agency_number.name='agency_number') LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority) WHERE COALESCE(contract_number.value,'') LIKE '%2473297%' ESCAPE '/' ORDER BY COALESCE(priority.value,'')='',CAST(priority.value AS signed),t.id; # Time: 100415 8:53:36 # u...@host: trac[trac] @ [xx.xx.xx.xx] # Query_time: 9 Lock_time: 0 Rows_sent: 1 Rows_examined: 0 The explain command says: +----+-------------+---------------+--------+---------------+--------- +---------+-----------------------+-------- +---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+--------+---------------+--------- +---------+-----------------------+-------- +---------------------------------+ | 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 168357 | Using temporary; Using filesort | | 1 | SIMPLE | contract_number | eq_ref | PRIMARY | PRIMARY | 172 | trac.t.id,const | 1 | Using where | | 1 | SIMPLE | agency_number | eq_ref | PRIMARY | PRIMARY | 172 | trac.t.id,const | 1 | | | 1 | SIMPLE | priority | eq_ref | PRIMARY | PRIMARY | 336 | const,trac.t.priority | 1 | | +----+-------------+---------------+--------+---------------+--------- +---------+-----------------------+-------- +---------------------------------+ 4 rows in set (0.00 sec) Thanks, Lucas On Mar 11, 5:53 pm, Lukasz Szybalski <[email protected]> wrote: > On Mar 10, 10:41 am, LukaszSzybalski<[email protected]> wrote: > > > > > You are correct. > > > Solution to problem 1. > > Unable to search for custom field number without running query twice? > > > In order to enter the contract# on the wiki page we need to build a > > form using get method that will post the result to query module. We > > use raw html functionality of a wiki. Here is the form. I've updated > > thehttp://trac.edgewall.org/wiki/WikiProcessorswithexample #4. This > > is extremely useful for us, and will cut down our search usage by > > half. > > > {{{ > > #!html > > <form action="/query" method="get"> > > <input type="text" name="contract_number" value="~" size="30"> <input > > type="submit" value="Search by Contract#"> > > </form> > > > }}} > > And to control what fields are displayed on the query screen use > hidden fields in the form. Trac is awsome!!!! Its great how various > technologies just fit in and its just works!!!! :) > > <input type="hidden" name="col" value="id"> > <input type="hidden" name="col" value="summary"> > <input type="hidden" name="col" value="status"> > <input type="hidden" name="col" value="milestone"> > <input type="hidden" name="col" value="version"> > <input type="hidden" name="col" value="owner"> > <input type="hidden" name="col" value="priority"> > <input type="hidden" name="col" value="component"> > > Thanks, > Lucas > > > On Mar 1, 8:36 am, CM Lubinski <[email protected]> wrote: > > > > -----BEGIN PGP SIGNED MESSAGE----- > > > Hash: SHA1 > > > > We've resorted to using raw html to create a form which submits to a > > > report with a dynamic variable [1]. > > > > You might look into a thread from a few months ago [2], which increases > > > the speed of search results. > > > > Good luck, > > > CM Lubinski > > > > [1]http://trac.edgewall.org/wiki/TracReports#AdvancedReports:DynamicVari... > > > [2]http://groups.google.com/group/trac-users/browse_thread/thread/348016... > > > > LukaszSzybalskiwrote: > > > > > On Feb 26, 1:59 am, Rowan <[email protected]> wrote: > > > >> In response to your points > > > > >> 1. To get the results of the query it has to be run, so no. > > > > I wish there was a way to pull up a "http://example.com/trac/query? > > > > status=new&status=reopened&order=priority&milestone=MyCategory_Other&contract_number=123" > > > > but wait for user to enter the contract# into the field before query > > > > gets run. > > > > > aka: > > > >http://trac.edgewall.org/query?priority=highest&priority=high&status=... > > > > but user wants a different ticket# > > > > >> 2. That's the nature of MySQL each time an update is run on a table > > > >> the query cache is cleared for any queries that are run on that table. > > > > ok > > > > >> 3. I've not investigated trac's database structure but that is one > > > >> long query, what is the actual aim of the query? > > > > > This is a query that trac is making by default. When you click on > > > > "view tickets" then custom query" , add your custom field in a query > > > > ui, and the sql statement I've email will run. Actually what I found > > > > out is that mysql TEXT field (custom field) cannot be stored in > > > > memory, and by switching it to varchar you can increase the > > > > performance of the query to 0.1 sec. #6986 > > > > I'm just not sure if that is true or not, and whether it can be done > > > > without breaking trac? > > > > > Thanks, > > > > Lucas > > > > -----BEGIN PGP SIGNATURE----- > > > Version: GnuPG v1.4.9 (GNU/Linux) > > > Comment: Using GnuPG with Mozilla -http://enigmail.mozdev.org > > > > iEYEARECAAYFAkuL0QoACgkQfzi1OiZiJLD7yACeOQ9XPofFWD7zwikzY3W3RMKh > > > QT0An1CL6Mh4FtAkn0RiTMJmehxH81Sw > > > =2jRF > > > -----END PGP SIGNATURE----- -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
