[Solved] Trying SQL query with subquery in WHERE clause

EverSQL Database Performance Knowledge Base

Trying SQL query with subquery in WHERE clause

For a message system we would like to know if a conversation allready exists in the database. The user will then be posting to the existing conversation instead of a new one.

Now, we have linked all participants to conversations. So on creating a new one, we count the number of people marked as participants (included current user (:totalUserCount)), en then would like a query to check if a conversation with x participants and all marked user(name)s exist before creating a new conversation.

After a few attempts we have a query that executes, but the result is not correct.

SELECT `app_company_user_cons`.* 
FROM (`app_company_user_cons`) 
LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_user_cons`.`id` = `link_company_user_cons_company_users`.`company_user_conversation_id` 
LEFT OUTER JOIN `app_company_users` app_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 

WHERE :totalUserCount = (SELECT 
    COUNT(*) 
    FROM (`app_company_users`) 
    LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 
    WHERE link_company_user_cons_company_users.company_user_conversation_id = app_company_user_cons.id 
) 
AND `link_company_user_cons_company_users`.`company_user_id` =  :currentUserID 
AND `app_company_users`.`name` =  :otherUserID2
AND `app_company_users`.`name` =  :otherUserID3
AND `app_company_users`.`name` =  :otherUserIDn 
LIMIT 1

The EXPLAIN function shows me: 

+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| id | select_type        | table                                | type   | possible_keys | key     | key_len | ref                                                               | rows | Extra                                               |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 1  | PRIMARY            | NULL                                 | NULL   | NULL          | NULL    | NULL    | NULL                                                              | NULL | Impossible WHERE noticed after reading const tables |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | link_company_user_cons_company_users | ALL    | NULL          | NULL    | NULL    | NULL                                                              | 2    | Using where                                         |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | app_company_users                    | eq_ref | PRIMARY       | PRIMARY | 4       | de1210mo_CRM.link_company_user_cons_company_users.company_user_id | 1    | Using where; Using index                            |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+

Not sure if we are missing something. Any ideas on how to solve this? Thanks in advance!

(PS: Our app adds AND 'app_company_users'.'name' = for all participants in an array)

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Correlated Subqueries (query line: 13): A correlated subquery is a subquery that contains a reference (column: id) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
  2. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  4. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `link_company_user_cons_company_users`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
  5. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `app_company_users`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
Optimal indexes for this query:
ALTER TABLE `app_company_user_cons` ADD INDEX `app_user_idx_id` (`id`);
ALTER TABLE `app_company_users` ADD INDEX `app_users_idx_name_id` (`name`,`id`);
ALTER TABLE `link_company_user_cons_company_users` ADD INDEX `link_user_idx_company_id_company_id` (`company_user_id`,`company_user_conversation_id`);
The optimized query:
SELECT
        `app_company_user_cons`.* 
    FROM
        (`app_company_user_cons`) 
    INNER JOIN
        `link_company_user_cons_company_users` link_company_user_cons_company_users 
            ON `app_company_user_cons`.`id` = `link_company_user_cons_company_users`.`company_user_conversation_id` 
    INNER JOIN
        `app_company_users` app_company_users 
            ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 
    WHERE
        :totalUserCount = (
            SELECT
                COUNT(*) 
            FROM
                (`app_company_users`) 
            LEFT OUTER JOIN
                `link_company_user_cons_company_users` link_company_user_cons_company_users 
                    ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 
            WHERE
                link_company_user_cons_company_users.company_user_conversation_id = app_company_user_cons.id
        ) 
        AND `link_company_user_cons_company_users`.`company_user_id` = :currentUserID 
        AND `app_company_users`.`name` = :otherUserID2 
        AND `app_company_users`.`name` = :otherUserID3 
        AND `app_company_users`.`name` = :otherUserIDn LIMIT 1

Related Articles



* original question posted on StackOverflow here.