Issues with a very large SQL query

Question

Problem:

I'm creating a Trac report that shows how many tickets are at each stage in our development cycle per chapter of our library. A tickets represents a single piece of work, usually an individual routine.

For instance how many tickets for the upcoming release (milestone) are at the peer review stage for chapter X.

There are 10 development stages and 47 chapters.

The given MySQL query is for all 10 development stages but for only one chapter and is 25 lines long, the whole query for all chapters is therefore over 1200 lines.

The error given by Trac is KeyError: 'numrows' where the query gets to large.

When entering the query directly into MySQL the error given is Out of resources when opening file (Errcode: 24) (23)

Question :

Refactoring - can this be done 'better' sql gurus, are there some clever tricks/advance techniques?
Approach - do I need a different approach completely?
Configuration - can MySQL and/or Trac be configured to accept very large queries

Notes:

The data in the tables is small, the query doesn't take long to execute when it's under the apparent size limitations.

The query is passed from the Trac system to MySQL which places some restrictions on what can be done, for example only a single query can be sent from trac to generate a report.

An example of what a Trac report looks like can be seen here.

The %c%* in the query is just the unique string I use for replacing the actual chapters when the query is generated via a script.

SELECT '%c%' as Chapter,
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status IN ('new','assigned') ) AS 'New',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='document_interface' ) AS 'Document\
 Interface',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='interface_development' ) AS 'Inter\
face Development',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='interface_check' ) AS 'Interface C\
heck',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='document_routine' ) AS 'Document R\
outine',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='full_development' ) AS 'Full Devel\
opment',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='peer_review_1' ) AS 'Peer Review O\
ne',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%'AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='peer_review_2' ) AS 'Peer Review Tw\
o',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='qa' ) AS 'QA',
(SELECT count(ticket.id) AS Matches FROM engine.ticket INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%'AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='closed' ) AS 'Closed',
count(id) AS Total,
ticket.id AS _id
FROM engine.ticket
INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine'

score 3 · Answer 1 · answered Sep 26 '12 at 18:18

Instead of making a subquery for every count, use a case to count from the data that already is fetched for the query:

select '%c%' as Chapter,
  sum(case when ticket.status IN ('new','assigned') then 1 else 0 end) as 'New',
  sum(case when ticket.status='document_interface' then 1 else 0 end) as 'DocumentInterface',
  sum(case when ticket.status='interface_development' then 1 else 0 end) as 'Interface Development',
  sum(case when ticket.status='interface_check' then 1 else 0 end) as 'Interface Check',
  sum(case when ticket.status='document_routine' then 1 else 0 end) as 'Document Routine',
  sum(case when ticket.status='full_development' then 1 else 0 end) as 'Full Development',
  sum(case when ticket.status='peer_review_1' then 1 else 0 end) as 'Peer Review One',
  sum(case when ticket.status='peer_review_2' then 1 else 0 end) as 'Peer Review Two',
  sum(case when ticket.status='qa' then 1 else 0 end) as 'QA',
  sum(case when ticket.status='closed' then 1 else 0 end) as 'Closed',
  count(id) as Total,
  ticket.id as _id
from
  engine.ticket
  inner join engine.ticket_custom on ticket.id = ticket_custom.ticket
where
  ticket_custom.name='chapter' and
  ticket_custom.value LIKE '%c%' and
  type='New material' and
  milestone='1.1.12' and
  component NOT LIKE 'internal_engine'

score 2 · Answer 2 · answered Sep 26 '12 at 18:10

Not going to completely rewrite it... but here's my suggestion:

SELECT '%c%' as Chapter,
    SUM(CASE WHEN ticket.status IN ('new','assigned') THEN 1 ELSE 0 END) as `New`,
    ...
    SUM(CASE WHEN ticket.status='closed' THEN 1 ELSE 0 END) as 'Closed',
    count(id) AS Total,
    ticket.id AS _id
FROM engine.ticket
INNER JOIN engine.ticket_custom 
    ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' 
    AND ticket_custom.value LIKE '%c%' 
    AND type='New material' 
    AND milestone='1.1.12' 
    AND component NOT LIKE 'internal_engine'
GROUP BY ticket.id
;

bta · Answer 3 · 2012-09-28T18:03:50.830

When it comes to generating complex reports like this through Trac, it's better to not use a report at all. Reports are OK for relatively simple queries, but they become unwieldy when you are organizing that many different stages and chapters.

Instead, try creating your "report" using a wiki page. This will give you more control over layout and presentation, plus you can avoid writing SQL altogether. Here's some sample wiki code for a faux report that shows all non-closed tickets grouped by milestone and then by status:

= Custom Report =
My custom report, as a wiki page

== Tickets for Milestone A ==
[[TicketQuery(milestone=MilestoneA,status!=closed,group=status,format=table)]]

== Tickets for Milestone B ==
[[TicketQuery(milestone=MilestoneB,status!=closed,group=status,format=table)]]

...

I don't know how you have your stages and chapters defined, so you'll need to tweak the query parameters to use the appropriate ticket fields. You can also play around with format and other options to tweak the output format.

That's just a simple example. The TicketQuery macro is capable of generating more complex reports. For an example of something that's probably closer (complexity-wise) to what you are looking for, take a look at the Trac project's release notes. The entire "Detailed List of Changes" section is generated with a single TicketQuery macro (hit the 'edit' button to see how they did it).

Another example

To re-create the chart that you linked to, you can do something like this:

||= **Id** =||= **Enhancements** =||= **Defects** =||= **Tasks** =||
||[milestone:v1.0 v1.0] || [[TicketQuery(milestone=v1.0,type=enhancement,format=count)]] || [[TicketQuery(milestone=v1.0,type=defect,format=count)]] || [[TicketQuery(milestone=v1.0,type=task,format=count)]] ||
... repeat for each milestone ...

Essentially, using format=count in the macro gives you what the database is returning for one of the sum(case ...) statements in Guffa's answer.

The big benefit of doing this using macros instead of direct SQL is that it becomes database-agnostic. You don't have to worry about differences between database engines (mysql, sqlite, etc), changes in Trac's database layout, special code for handling custom fields, etc.

This might be the way to go however the trac query language is to restrictive for what I need to do. — Chris Seymour, Sep 28 '12 at 07:50

score 0 · Answer 4 · answered Sep 26 '12 at 18:06

Yeek, that's ugly. Do you HAVE to fetch all that data in a single row? That many subqueries is going to slam the server. Can you not do a standard grouped fetch and do the pivot table stuff client-side?

e.g.

SELECT count(ticket.id) AS Matches, ticket_custom.name, ticket.status
FROM engine.ticket
INNER JOIN engine.ticket_custom ON ticket.id = ticket_custom.ticket
WHERE ticket_custom.name='chapter' AND ticket_custom.value LIKE '%c%' AND type='New material' AND milestone='1.1.12' AND component NOT LIKE 'internal_engine' AND ticket.status='qa' AND (ticket.status IN (........))
GROUP BY ticket.id, ticket_custom.name

then, in pseudo-code:

data = array()
while(row = fetch($result)) {
   data[ticket.id]][ticket.status] = row[ticket.status];
}

This way you'd be running only one single query, and doing some minor extra work in the fetch logic to recreate what your original query is laboring to do.

I know it's very ugly but this is due to Tracs limitations, Trac is the web front end to the database and to create a report you pass your query through a textbox from your trac site. Therefore it doesn't allow you to do the processing client-side. — Chris Seymour, Sep 26 '12 at 18:13

score 0 · Accepted Answer · answered Oct 03 '12 at 15:13

I ended up writing my own trac reporting system in php. This allows a lot more flexibility to create the kind of reports I need and doesn't involve ridiculous mysql queries. Too intergrate the script with the trac project the report items i.e the number of tickets at stage QA in chapter XX are hyperlinks to the tickets themselves in trac: http://myhost.co.uk/trac-project/query?id=10&id=15. Also the NavAddPluggin allows the main trac navigation bar to be customized so a menu option that links to the script can be added.

Issues with a very large SQL query

5 Answers5