Xrhrft

What is the best way to get count for all the records, where case evaluates to one for all the conditions. I have the query as:

SELECT  

    uuid , 

    COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Age_Group' and level2 = '18-24' ) THEN 1 END) AS 'Profile > Age > 18-24',

    COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Age_Group' and level2 = '25-34' ) THEN 1 END) AS 'Profile > Age > 25-34',

    COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Gender' and level2 = 'M' ) THEN 1 END) AS 'Profile > Gender > M',

    COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'State' and level2 = 'QLD' ) THEN 1 END) AS 'Profile > State > QLD',

    COUNT( CASE WHEN (dt = 2 and level0 = 'Insurance'  and level1 = 'Health' ) THEN 1 END) AS 'Interest > Insurance > Health',

    COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Kids' ) THEN 1 END) AS 'Profile > Kids'

FROM segmentdata sd

WHERE sd.created >= DATE_SUB(NOW(), INTERVAL 365 DAY)

GROUP BY sd.uuid;

The above query gives me a 1 and 0. I need to get a list of all one, for all the conditions and count them.

I have tried:

SELECT 

    COUNT(CASE WHEN ((asd.`Profile > Age > 18-24` OR asd.`Profile > Age > 25-34`) 

        AND (asd.`Profile > Gender > M` AND asd.`Profile > State > QLD` 

        AND asd.`Interest > Insurance > Health`)

        AND NOT (asd.`Profile > Kids`)) THEN 1 END )

FROM (

    SELECT

        uuid , 

            COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Age_Group'  and level2 = '18-24' )  THEN 1 END) AS 'Profile > Age > 18-24',

            COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Age_Group'  and level2 = '25-34' ) THEN 1 END) AS 'Profile > Age > 25-34',

            COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Gender'  and level2 = 'M' ) THEN 1 END) AS 'Profile > Gender > M',

            COUNT( CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'State'  and level2 = 'QLD' ) THEN 1 END) AS 'Profile > State > QLD',

            COUNT(CASE WHEN (dt = 2 and level0 = 'Insurance'  and level1 = 'Health' ) THEN 1 END) AS 'Interest > Insurance > Health',

            COUNT(CASE WHEN (( dt = 1 or level0 = 'Profile' ) and level1 = 'Kids' ) THEN 1 END) AS 'Profile > Kids'

         FROM segmentdata sd

         WHERE sd.created >= DATE_SUB(NOW(), INTERVAL 365 DAY)

         GROUP BY sd.uuid

     ) asd

But the above query takes a lot of time! What is the best way to do this? The above query takes about 1 -2 minutes. I need to reduce that time drastically. Please help. This is quite urgent.

My desired results are that the query runs as quickly as possible as well as gives me a count. For eg the above should give me the count from the table 'segmentdata' for All 18-34 males in QLD interested in Health insurance, without kids.

Thanks

I'm just guessing at your data structure from your query, but it looks like it'd be a lot easier to use derived tables for your problem. Here's what it should roughly look like:

select 

    count(distinct age.uuid)

from

    (

        select uuid

        from segmentdata

        where ( dt = 1 or level0 = 'Profile' ) 

            and level1 = 'Age_Group' 

            and level2 in ('18-24', '25-34')

    )  age

    inner join

    (

        select uuid

        from segmentdata

        where ( dt = 1 or level0 = 'Profile' ) 

            and level1 = 'Gender' 

            and level2 = 'M'

    ) gender

        on age.uuid = gender.uuid

This queries creates several derived tables based on what you want to filter on, and then joins all of the uuid's together. In effect, you should only get uuid's that are in all of the derived tables.

If this doesn't perform fast enough, then try creating an index on: uuid, dt, level0, level1, level2, and created

Use SUM() instead of COUNT() when the expression evaluates to 0 or 1. That is...

COUNT(CASE WHEN condition THEN 1 END)

-->

SUM(condition)

This should help performance:

INDEX(created, uuid)

Perhaps you don't have a big enough cache? SHOW VARIABLES LIKE '%buffer%'; and how much RAM do you have?

Please show us SHOW CREATE TABLE segmentdata for further discussion.