CODEWITHSUNDEEP
mysqloptimizationyii2query-builder
Dennis John
Dennis John

Reputation: 1

Optimize query for yii2

this query is getting the right data i want, but this is so slow and heavy on our server, with only 5417 rows of data. how would i optimize this query in query builder or yii2 framework way?

SELECT  t1.id
    FROM  tbl_ticket t1
    WHERE  NOT EXISTS 
        ( SELECT  1
            FROM  tbl_ticket
            WHERE  parent_id = t1.id 
        )
      AND  parent_id =0
    UNION  ALL 
SELECT  MAX( id )
    FROM  tbl_ticket
    WHERE  parent_id <>0
    GROUP BY  parent_id
    ORDER BY  id ASC

Upvotes: 0

Views: 673

Answers (2)

Dennis John
Dennis John

Reputation: 1

here is the table:

CREATE TABLE IF NOT EXISTS tbl_ticket ( id int(9) NOT NULL AUTO_INCREMENT, parent_id int(11) NOT NULL, ticket_title varchar(250) NOT NULL, location_id varchar(250) CHARACTER SET latin1 NOT NULL, status int(11) NOT NULL, remarks varchar(250) CHARACTER SET latin1 NOT NULL, r_date datetime NOT NULL, d_date datetime NOT NULL, hd_user_username varchar(250) CHARACTER SET latin1 NOT NULL, hd_user_email varchar(250) CHARACTER SET latin1 NOT NULL, description varchar(3000) NOT NULL, attachment varchar(250) NOT NULL, created_by varchar(250) NOT NULL, updated_by varchar(250) NOT NULL, room_no varchar(250) NOT NULL, gsm varchar(250) NOT NULL, file_mime_type varchar(250) NOT NULL, file_original_name varchar(250) DEFAULT NULL, file_size varchar(250) DEFAULT NULL, file_extension varchar(250) DEFAULT NULL, acknowledged varchar(250) DEFAULT NULL, ip_address varchar(20) DEFAULT NULL, hostname varchar(255) DEFAULT NULL, useragent text, closing_remark varchar(2048) DEFAULT NULL, rating int(6) DEFAULT NULL, is_student int(2) DEFAULT NULL, installation int(1) DEFAULT NULL, type int(1) DEFAULT NULL, PRIMARY KEY (id), KEY id (id), KEY parent_id (parent_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5428 ;

Upvotes: 0

Rick James
Rick James

Reputation: 142366

( SELECT  t1.id
    FROM      tbl_ticket t1
    LEFT JOIN tbl_ticket t2  ON  t2.parent_id = t1.id 
    WHERE t2.id IS NULL
      AND t1.parent_id = 0 )
UNION  ALL 
( SELECT  MAX( id )
    FROM  tbl_ticket
    WHERE  parent_id <> 0
    GROUP BY  parent_id )
ORDER BY id ASC

The first SELECT seems to be finding ids that have no children and no parents. Correct?

The second SELECT seems to be finding the last sibling in each family, excluding Adam and Eve. Is that what you intended?

I added parentheses to move the ORDER BY away from the second SELECT to the entire query. Is that what you intended?

To make that query run 'fast', you need

INDEX(parent_id, id)

I assume, you already have PRIMARY KEY(id). (Please provide SHOW CREATE TABLE tbl_ticket.)

Upvotes: 0

Related Questions