CODEWITHSUNDEEP
phpmysqlinsert
Andreas
Andreas

Reputation: 559

SQL Nested insert query

I have today a database with 3 queries for statistics in testcases. I use php to build a website but the queries takes too long to execute, so I cannot show the complete site statistics as I wan't to do. As it is now the database is updated rarely in batches so I could make events that updates the databases daily / weekly for this purpose.

I have the following 3 queries today to display the information I want:

SELECT key FROM testcase GROUP BY key;

This gives me a list with int from 4-10 groups of tests. say 1,2,3,4 for the case of argument. I then iterate this in the subqueries as key to get the number of tests passed and number of tests not passed with the following 2 queries:

SELECT COUNT(*) AS passed FROM testcase INNER JOIN testcases ON 
  testcase.ID = testcases.testcaseid WHERE pass = 1 AND key = %value%;

and

SELECT COUNT(*) AS failed FROM testcase INNER JOIN testcases ON 
  testcase.ID = testcases.testcaseid WHERE pass = 0 AND key = %value%;

this is how it works today. The queries takes around 25-30 seconds for each key and that makes the site timeout. (%value% is pseudo code for the current value from the for loop)

Instead I was thinking of a sql query that added this to a database table that consists of key,pass,fail tables that i populate daily/weekly to show on the site as statistics. I have seen some cases where you can iterate with a subquery but as the 2 executed queries is opposites I cannot see any solution for this.

I have tried indexing the values I use in the queries with no success (which could be wrong as well).

// Andreas (SQL novice)

Edit:

Oh the dilemma, where to set the solution, I made a combine from Gordon and Joe to make the fastest query that worked like a charm:

INSERT INTO statistics (key,passed,failed)
  SELECT
    key,
    SUM(case when T.pass = 1 then T.matches else 0 end) as passed,
    SUM(case when T.pass = 0 then T.matches else 0 end) as failed
  FROM
    (SELECT
      key,pass,COUNT(*) AS matches
    FROM testcase INNER JOIN testcases ON
      testcase.ID = testcases.testcaseid
    GROUP BY key,pass)T
  GROUP BY key

Upvotes: 1

Views: 588

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you should do this all with one query, that returns the key and the information you want:

select key,
         SUM(case when pass = 1 AND key = %value% then 1 else 0 end) as passed,
         SUM(case when pass = 0 AND key = %value% then 1 else 0 end) as failed
from testcase INNER JOIN testcases ON 
      testcase.ID = testcases.testcaseid 
group by key

To the extent possible, you should let the database do the "iterations" and just read back the resulting data.

In response to your comment. If you have a list of keys in $VALUE, then you could do:

insert into statistics(key, pass, fail)
    select key,
           SUM(case when pass = 1 then 1 else 0 end) as passed,
           SUM(case when pass = 0 then 1 else 0 end) as failed
    from testcase INNER JOIN testcases ON 
         testcase.ID = testcases.testcaseid 
    where concat(',', $VALUE, ',') like concat('%', key, '%')
    group by key

Or, if you are generating the query, you can put the list in an in clause. So, the where could look like:

where key in (2, 3, 4, 5)

Upvotes: 1

000
000

Reputation: 27247

Try:

select key, pass, count(*) from testcase group by key, pass

An index on pass is going to be useless. Index on key, that should help a little. But with a cardinality of 4-10, meh, not going to be super helpful.

Upvotes: 1

Related Questions