Issues with a very large SQL query

mysql sql refactoring trac
Chris Seymour picture Chris Seymour · Sep 26, 2012 · Viewed 7.5k times · Source

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'

Answer

Guffa picture Guffa · Sep 26, 2012

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'

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More