1

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

  • how much data is this running on? Also, are there any indexes on the table? – DForck42 Jan 26 '15 at 22:35
  • There are about 2 million and over records and the indices on the table are like: <code> KEY HASHINDEX (hash), KEY UUIDINDEX (uuid), KEY CREATEDINDEX (created), KEY UUID_CREATEDINDEX (uuid,created) </code> – Newbie Jan 26 '15 at 22:57
0

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

  • sqlfiddle.com/#!2/8b5aaaf/3 – Newbie Jan 29 '15 at 1:30
  • @user57391 I don't knwo what you want me to see – DForck42 Jan 29 '15 at 5:47
  • The SQLFiddle has a few data that I want to do a select count on. I am trying to see if there is a better way, this can be done like, or if there is any other way! – Newbie Jan 29 '15 at 20:54
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.