Skip to main content
added tags to users I mentioned
Source Link

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.

Edit: I want to highlight both MT0's@MT0 and Kondybas'@Kondybas solutions as they both work, but just did not run as fast as the solution I found on my specific data and architecture (but may work for others with similar issues).

MT0's solution is great because it avoids JOINS entirely, but the aggregate functions ran slow on the queries I made using the conditional aggregate approach. The View created with conditional aggregates took ~15s to load. I am running ~30 functions every time the View is loaded in addition to other components of the query which join with other tables.

Kondybas' solution with the self JOINs ran on average 2.3s after some testing which is really good! I do think the query is easier to read and understand compared to the solution I found.

My final solution averaged ~450ms for the query. I think highlighting different viable solutions is important, even though I did not select them as the final answer.

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.

Edit: I want to highlight both MT0's and Kondybas' solutions as they both work, but just did not run as fast as the solution I found on my specific data and architecture (but may work for others with similar issues).

MT0's solution is great because it avoids JOINS entirely, but the aggregate functions ran slow on the queries I made using the conditional aggregate approach. The View created with conditional aggregates took ~15s to load. I am running ~30 functions every time the View is loaded in addition to other components of the query which join with other tables.

Kondybas' solution with the self JOINs ran on average 2.3s after some testing which is really good! I do think the query is easier to read and understand compared to the solution I found.

My final solution averaged ~450ms for the query. I think highlighting different viable solutions is important, even though I did not select them as the final answer.

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.

Edit: I want to highlight both @MT0 and @Kondybas solutions as they both work, but just did not run as fast as the solution I found on my specific data and architecture (but may work for others with similar issues).

MT0's solution is great because it avoids JOINS entirely, but the aggregate functions ran slow on the queries I made using the conditional aggregate approach. The View created with conditional aggregates took ~15s to load. I am running ~30 functions every time the View is loaded in addition to other components of the query which join with other tables.

Kondybas' solution with the self JOINs ran on average 2.3s after some testing which is really good! I do think the query is easier to read and understand compared to the solution I found.

My final solution averaged ~450ms for the query. I think highlighting different viable solutions is important, even though I did not select them as the final answer.

Added edit section that highlighted other viable solutions. Important b/c they work, are completely viable, and may run better on someone else's architecture.
Source Link

The post by MT0 works, but the conditional aggregations slow down my query dramatically, even with indexing. I wanted to mention it in case anyone wants to try it as a viable alternative to the answer I found.

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.

Edit: I want to highlight both MT0's and Kondybas' solutions as they both work, but just did not run as fast as the solution I found on my specific data and architecture (but may work for others with similar issues).

MT0's solution is great because it avoids JOINS entirely, but the aggregate functions ran slow on the queries I made using the conditional aggregate approach. The View created with conditional aggregates took ~15s to load. I am running ~30 functions every time the View is loaded in addition to other components of the query which join with other tables.

Kondybas' solution with the self JOINs ran on average 2.3s after some testing which is really good! I do think the query is easier to read and understand compared to the solution I found.

My final solution averaged ~450ms for the query. I think highlighting different viable solutions is important, even though I did not select them as the final answer.

The post by MT0 works, but the conditional aggregations slow down my query dramatically, even with indexing. I wanted to mention it in case anyone wants to try it as a viable alternative to the answer I found.

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.

Edit: I want to highlight both MT0's and Kondybas' solutions as they both work, but just did not run as fast as the solution I found on my specific data and architecture (but may work for others with similar issues).

MT0's solution is great because it avoids JOINS entirely, but the aggregate functions ran slow on the queries I made using the conditional aggregate approach. The View created with conditional aggregates took ~15s to load. I am running ~30 functions every time the View is loaded in addition to other components of the query which join with other tables.

Kondybas' solution with the self JOINs ran on average 2.3s after some testing which is really good! I do think the query is easier to read and understand compared to the solution I found.

My final solution averaged ~450ms for the query. I think highlighting different viable solutions is important, even though I did not select them as the final answer.

Source Link

The post by MT0 works, but the conditional aggregations slow down my query dramatically, even with indexing. I wanted to mention it in case anyone wants to try it as a viable alternative to the answer I found.

My Solution: MySQL 5.1 apparently allows nested subqueries in a View as long as they part of the SELECT. Rewriting the JOINS as nested SELECTS like this:

 SELECT submission_id,
 (
    SELECT response as is_reporting
    FROM frm_form_attribute_data
    WHERE attribute_id = 382 AND submission_table.submission_id = submission_id
 ) AS is_reporting,
 (
    SELECT response as email
    FROM frm_form_attribute_data
    WHERE attribute_id = 385 AND submission_table.submission_id = submission_id
 ) AS email
 FROM submission_table;

returns the expected output. Every Inner JOIN gets moved to the SELECT part of the query and the JOIN ON condition is moved into the nested SELECT as a WHERE condition.

I did some testing and this method provides roughly similar performance to the original query I posted with the JOINS.