MySQL – Subquery with Variable in WHERE Clause

MySQLtabletable variablewhere

I don't know why but if I do the subquery where in the first LEFT JOIN with the value of the variable @id it does not return any results, it is as if the value of @id was always null.

But why not do the LEFT JOIN directly?

Simple, the table is large and the query is taking horrors to execute, so it would be much faster.

SELECT
    DISTINCT @id := p.id_pen,
    p.id_env_pen,
   
    0 AS id_star,
    FALSE AS is_star,
       
    u.id_user AS sender_id,
    u.name_user AS sender_name,

    u2.id_user AS response_id,
    u2.name_user AS response_name,
       
    p.text_pen AS text_send,
    p2.text_pen AS text_response,
       
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,
       
    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
    LEFT JOIN (
        SELECT 
            p2.id_pen,
            p2.text_pen,    
            p2.created_at_pen,
            p2.id_pen_req_pen
        FROM pendency AS p2
        WHERE p2.id_pen_req_pen = @id 
        # HERE, I try do `= p.id_pen` but dont works too, 
        # id not identify the alias from outside
    ) AS p2 ON p2.id_pen_req_pen = @id
    
    INNER JOIN users AS u ON u.id_user = p.id_user_pen
    LEFT JOIN users AS u2 ON u2.id_user = p.id_user_req_pen
WHERE (p.id_user_pen = '5' AND p.id_user_req_pen != -10)
OR p.id_user_req_pen = '5'

CREATE TABLES

CREATE TABLE IF NOT EXISTS `users` (
    `id_user` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `id_fs_user` VARCHAR(30) DEFAULT 'Não informado',
    `email_user` VARCHAR(105) DEFAULT 'Não informado',
    `pass_user` VARCHAR(35) DEFAULT 'Não informado',
    `name_user` VARCHAR(120) DEFAULT 'Não informado',
    `cpf_user` VARCHAR(15) DEFAULT 'Não informado',
    `type_user` VARCHAR(15) DEFAULT 'master',
    `work_week_user` INT DEFAULT 5,
    `is_block_user` BOOLEAN DEFAULT FALSE,
    `permission_id_user` INT NOT NULL DEFAULT 0,
    `attendant_user` BOOLEAN DEFAULT FALSE,
    `version_id_user` INT NOT NULL DEFAULT 0,
    `last_login_user` DATETIME,
    `last_move_user` DATETIME,

    `id_user_user` INT DEFAULT 0,
    `created_at_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_date_user` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `safe_del_state_user` BOOLEAN DEFAULT FALSE,
    `safe_del_user_user` INT DEFAULT 0);

CREATE TABLE IF NOT EXISTS `pendency` (
    `id_pen` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `id_env_pen` INT NOT NULL,
    `text_pen` TEXT,
    `id_user_req_pen` INT DEFAULT 0,
    `id_pen_req_pen` INT DEFAULT 0,
    `time_res_pen` INT NOT NULL,
    `state_pen` BOOLEAN DEFAULT FALSE,
    `id_old_pen` INT NOT NULL,
    `id_user_pen` INT NOT NULL,
    `created_at_pen` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`id_env_pen`) REFERENCES `env`(`id_env`) ON DELETE CASCADE);

Best Answer

The problem with your query performance is that x = 5 or y = 5 queries don't usually use indexes.

We help this out with your code by creating greatest and least values as generated columns with indexes.

alter TABLE `pendency`
add lpen int as (least(id_user_pen,id_user_req_pen)),
add gpen int as (greatest(id_user_pen,id_user_req_pen)),
add index gpen(gpen),
add index lpen(lpen);

Then use a union, first to grap those with 5 as the greater user (talking to 1-4) and then 5 as the least talking to the others.

(
SELECT
    p.id_pen,
    p.id_env_pen,
    0 AS id_star,
    FALSE AS is_star,
    p.text_pen AS text_send,
    p2.text_pen AS text_response,   
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,
       
    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
JOIN pendency p2 ON
  p2.id_pen_req_pen = p1.id_pen
  AND p.gpen= 5
)
UNION ALL
(
SELECT
    p.id_pen,
    p.id_env_pen,
    0 AS id_star,
    FALSE AS is_star,
    p.text_pen AS text_send,
    p2.text_pen AS text_response,   
    DATE_FORMAT(p.created_at_pen, '%Y-%m-%d %H:%i:%s') AS send_datetime,
    DATE_FORMAT(p2.created_at_pen, '%Y-%m-%d %H:%i:%s') AS response_datetime,
       
    p.time_res_pen,
    p.state_pen,
    p.created_at_pen
FROM pendency AS p
JOIN pendency p2 ON
  p2.id_pen_req_pen = p1.id_pen
  AND p.lpen= 5
)

Joining to users, handling of (id_user_req_pen!= -10) and ordering has been left as an exercise to the reader you.

Related Question