Condition’s speed in query [closed]

Given both give the same result and from the same data. Does adding the condition at SELECT (CASE WHEN) make the query faster than putting it at WHERE?

/*On SELECT*/    
        SELECT
            COUNT(*) as totaal,
            SUM(general_score_filter.dash) as totaal_score
        FROM (
        SELECT 
            operatie.id as operatie_id,
            AVG(CASE WHEN patient.inactief = 0 AND vraag.eigenschap_id = _eigenschap_id THEN antwoord.score ELSE 0 END) AS dash
        FROM
        operatie
            LEFT JOIN antwoord ON operatie.id = antwoord.operatie_id AND antwoord.deleted = 0
            LEFT JOIN vraag ON vraag.id = antwoord.vraag_id AND vraag.deleted = 0
            LEFT JOIN patient ON operatie.patient_id = patient.id AND patient.inactief = 0
        GROUP BY operatie.id) AS general_score_filter;

/*On WHERE*/
        SELECT
            COUNT(*) as totaal,
            SUM(general_score_filter.dash) as totaal_score
        FROM (
        SELECT 
            operatie.id as operatie_id,
            AVG(CASE WHEN vraag.eigenschap_id = _eigenschap_id THEN antwoord.score ELSE 0 END) AS dash
        FROM
        operatie
            LEFT JOIN antwoord ON operatie.id = antwoord.operatie_id AND antwoord.deleted = 0
            LEFT JOIN vraag ON vraag.id = antwoord.vraag_id AND vraag.deleted = 0
            LEFT JOIN patient ON operatie.patient_id = patient.id AND patient.inactief = 0
        WHERE
            patient.inactief = 0
        GROUP BY operatie.id) AS general_score_filter;

Answer

It all depends on the amount of your data. Small dataset of 50k-100k, you may not notice, but if you had millions of rows, you would.

But overall, yes it would be faster in your WHERE clause with an index on the inactief status. Based on sample data below, you would be skipping out on all the “inactief = 1” records, but also, one would be more accurate for your answer. Do you want all people, or only those that inactief = 0. Read more below

Example with the following records from the Operatie table

id    inactief      score (via the join to antwoord)
1     0             10
1     0             20
1     1             30
1     0             40
1     1             50
1     1             60
1     0             70
2     1             15
2     1             25
3     0             38

Assuming your first query, your final count would have all 3 patients ( 1, 2 and 3 ). Your second count where clause of “inactief = 0” will only return 2 patients (1 and 3) since patient 2 had all their records with inactief = 1.