Turn a SQL optimization-CodePudding

Select * from feed_comment where
Feed_id in (select id from feed where user_id=5151)
Union all
Select * from feed_comment where to_id in (
Select the id from feed_comment where user_id=5151
)
Union all
Select * from feed_comment where id in (
The select to_id from feed_comment where to_id in (
Select the id from feed_comment where user_id=5151
))
The order by create_time desc

A complete SQL statements, query associated too much, ask how to improve the bosses

CodePudding user response:

Have a try

 select a. * from feed_comment a, (select id from feed where user_id=5151) b 
Where a.f eed_id=b.i d 
The or a.t o_id=b.i d 
Union all 
Select a. * from feed_comment a, (select id from feed where user_id=5151), b (select id, to_id from feed_comment) c 
Where Anderson, d=c.i d 
And c.t o_id=b.i d

CodePudding user response:

reference 1st floor qybao response:

have a try

 select a. * from feed_comment a, (select id from feed where user_id=5151) b 
Where a.f eed_id=b.i d 
The or a.t o_id=b.i d 
Union all 
Select a. * from feed_comment a, (select id from feed where user_id=5151), b (select id, to_id from feed_comment) c 
Where Anderson, d=c.i d 
And c.t o_id=b.i d

wrong oh, data query is not complete

CodePudding user response:

 select a. * from feed_comment a left join feed on b a.f eed_id=b.i d left join feed_comment c Anderson, d=c.t o_id where user_id=5151 order by a.c reate_time desc

Write a hope that under the table structure to provide the reference

CodePudding user response:

reference squid ing reply: 3/f

 select a. * from feed_comment a left join feed on b a.f eed_id=b.i d left join feed_comment c Anderson, d=c.t o_id where user_id=5151 order by a.c reate_time desc

A random write a hope to provide reference table structure

SQL write wrong, correct results also wrong;
The CREATE TABLE ` feed ` (
` id ` bigint (20) NOT NULL AUTO_INCREMENT,
` content ` varchar (255) the DEFAULT NULL,
` cover ` varchar (255) the DEFAULT NULL,
` create_time ` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
` location ` varchar (255) the DEFAULT NULL,
` medium ` varchar (500) NOT NULL,
` how ` bit (1) NOT NULL,
` status ` int (11) NOT NULL,
` topic_id ` bigint (20) DEFAULT NULL,
` update_time ` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
` user_id ` bigint (20) NOT NULL,
` video ` bit (1) NOT NULL,
The PRIMARY KEY (` id `)
) ENGINE=InnoDB AUTO_INCREMENT=2174 DEFAULT CHARSET=utf8mb4;

The CREATE TABLE ` feed_comment ` (
` id ` bigint (20) NOT NULL AUTO_INCREMENT,
` content ` varchar (255) the DEFAULT NULL,
` create_time ` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
` feed_id ` bigint (20) NOT NULL,
` to_id ` bigint (20) DEFAULT NULL,
` update_time ` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
` user_id ` bigint (20) NOT NULL,
The PRIMARY KEY (` id `)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4;

CodePudding user response:

refer to the second floor weixin_38316591 response:

wrong oh, incomplete data query

Look at the wrong name of the table, revise the

Select * from feed_comment where id in (
The select to_id from feed_comment where to_id in (
Select the id from feed_comment where user_id=5151
))
Are you sure you didn't write wrong? The same table, middle tier to_id in inner id, outer id again in the middle tier to_id, the inequivalence in outer id in the inner id, which is equivalent to the following SQL,
Select * from feed_comment where user_id=5151 - id consistent inside and outside layer is actually choose lining can

 -- so the whole can simplify 
Select * from feed_comment 
Where feed_id in (select id from feed where user_id=5151) 
The or to_id in (select id from feed_comment where user_id=5151) 
The or user_id=5151 

- or reduced to 
Select a. * from feed_comment a, (select id from feed where user_id=5151), b (select id from feed_comment where user_id=5151) c 
Where a.f eed_id=b.i d 
The or a.t o_id=c.i d 
The or a.u ser_id=5151

CodePudding user response:

reference 5 floor qybao reply:

Quote: refer to the second floor weixin_38316591 response:

Wrong oh, incomplete data query

Look at the wrong name of the table, revise the

Select * from feed_comment where id in (
The select to_id from feed_comment where to_id in (
Select the id from feed_comment where user_id=5151
))
Are you sure you didn't write wrong? The same table, middle tier to_id in inner id, outer id again in the middle tier to_id, the inequivalence in outer id in the inner id, which is equivalent to the following SQL,
Select * from feed_comment where user_id=5151 - id consistent inside and outside layer is actually choose lining can

-- so the whole can simplify
Select * from feed_comment
Where feed_id in (select id from feed where user_id=5151)
The or to_id in (select id from feed_comment where user_id=5151)
The or user_id=5151

- or reduced to
nullnullnullnullnullnullnullnullnullnullnullnull