How to Optimize MySQL Query with HAVING and GROUP BY

MySQL

My Task is to optimize a query, which should check for data inconsistency: Return all leads ids with more than one entries and multiple is_first flag.

| id | lead_id | is_first| (other data) |
|----|---------|---------|-------- ...
|   1|       20|        1|...
|   2|       20|        0|
|   3|       21|        1|
|   4|       21|        0|
|   5|       21|        1|
|   6|       22|        1|

So this dataset should return 21. (20 ok, because is_first is only true/1 once)

Old Query:

SELECT DISTINCT s1.lead_id as lead_id
FROM history as s1
INNER JOIN history as s2 ON
  s1.is_first = 1 AND
  s2.is_first = 1 AND
    s1.id != s2.id AND
    s1.lead_id = s2.lead_id;

New Query: I already increased performance somewhat with this query

SELECT history.lead_id AS lead_id
FROM history
WHERE is_first = 1 GROUP BY lead_id
HAVING count(lead_id) != 1;

But I feel it's not end of the line. Any recommendations?

Best Answer

If you dont have it already, you could create a index on both fields: is_first and lead_id;

If it's a table with few record you won't see much difference, so I always recommend to check the statistics from the query.

Related Solutions

MySQL – Query Performance with Multiple JOINs on MyISAM-Based DB

Foreign keys are not required in order to execute a JOIN. Foreign keys are restrictions that are there for security reasons (so that you cannot change the value of a column, or delete/insert a row ending up with an inconsistent state between tables). It is true that in most cases foreign key are used as JOIN matching conditions.

The reasons why InnoDB is recommended is because its support of data integrity thanks to these foreign keys and database transactions. It can also support better concurrency as writes do not need to lock the whole table in order to proceed, only the rows modified. That is why it is recommended for write-loads. If your tables are going to be read-only, transactions, integrity and concurrency are not so important (because you are not going to modify the data), that is why in those cases the simpler MyISAM format can work fine.

InnoDB also has its own buffering management, which can be fine-tuned better than with MyISAM, where its cache works only for indexes, leaving data caching management to the filesystem cache.

There is one case in which foreign key creation can improve your performance: on creation, InnoDB creates a secondary index on the column if it didn't have one beforehand. This functionality is as easy to overcome as creating one on any column that may be referenced on the JOIN.

In summary, InnoDB has many advantages, but in a read-only workload MyISAM can work properly. If you want the data security, however, you will have to implement that on a software layer, easily leading to errors, that is why foreign keys are always recommended.

MySQL – Multiple WHERE Conditions on Same Column

Your WHERE clause does not make much sense, because it is applied to each row individually and it is pointless to check if the same value is equal to a and at the same time not equal to b or c – of course, it will not be equal to b or c if it is a. What you want instead, therefore, is for the conditions to be applied to a group of rows as a whole – more specifically, to each group of rows sharing the same txn_id.

So, you need to use GROUP BY and, to apply conditions to groups of rows, HAVING. This query will give you the list of txn_id values matching your requirements:

SELECT
    t.txn_id
FROM
    1_txns AS t
    INNER JOIN 2_products AS p USING (sku)
GROUP BY
    t.txn_id
HAVING
    COUNT(p.rk_group = :group1 OR NULL) > 0
    AND COUNT(p.rk_group IN (:group2, group3) OR NULL) = 0
;

As you do not seem to want the list, only the number of its items, use the above as a derived table to count the rows:

SELECT
   COUNT(*)
FROM
(
    SELECT
        txn_id
    FROM
        1_txns AS t
        INNER JOIN 2_products AS p USING (sku)
    GROUP BY
        t.txn_id
    HAVING
        COUNT(rk_group = :group1 OR NULL) > 0
        AND COUNT(rk_group IN (:group2, group3) OR NULL) = 0
) AS s
;

As you can see, COUNT(DISTINCT ...) is not necessary: the derived table is grouping by txn_id and thus cannot return duplicates – so, COUNT(*) is enough to get the correct result.

In case you are not aware, the OR NULL bit lets the COUNT function count only matches and omit mismatches, as explained in detail in this answer:

Why do I need “OR NULL” in MySQL when counting rows with a condition

Best Answer

Related Solutions

MySQL – Query Performance with Multiple JOINs on MyISAM-Based DB

MySQL – Multiple WHERE Conditions on Same Column

Related Question