I'm not going to be able to explain the details, but Google for
"optimizing slow queries in MySQL" and read the lists of ways to do so
that are returned. The key things I understand about query optimization
are:
1. Pick the slowest query from your slow query log, and run EXPLAIN
SELECT on it.
2. Understand how MySQL analyzes your query and decides what strategy
to use (what columns and indexes it will filter the results on
first), then either
3. Index the columns that are used first in that strategy but are not
yet indexed, or
4. Restructure your query so that MySQL's strategy will filter the
results down to a small set early (rather than late) in the
query's execution (in the first columns used to filter the results).
It will take you a while to get the hang of how to optimize queries, but
it is very worthwhile to do, because it will turn a 5 minute query into
a 0.001 second query, and render your application usable again! : )
Tim
On 04/15/2010 04:45 PM, Lukasz Szybalski wrote:
> 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:
>>
--
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.
