Dee.Lee Dee.Lee - 1 month ago 17
MySQL Question

Merge the query results,optimize?

mysql,two tables´╝Ütest(one) and review(many).
My goal:from review for one in the corresponding number

SELECT t.ID,t.TITLE,COALESCE(COUNT(r.ID),0) `count`
FROM `test` t
LEFT OUTER JOIN review r
ON t.ID = r.REVIEW_OBJ_ID
WHERE r.REVIEW_TYPE = '4'
ORDER BY `count` DESC;


Output:

ID TITLE count
402884f657e0a6d20157e0a82cc90000 brother 2


test table(A small portion of the data)

SELECT t.ID,t.TITLE
FROM `test` t;


Output:

ID TITLE
40284c8157ad8e7d0157ad8f86880000 1234567890123456789012345
402884f657e0a6d20157e0a82cc90000 brother
402884f657e0a6d20157e11967a20036 fg
402884f657e51eff0157e54cd8610004 AAA
402884f657e652fb0157e65642750000 BBB
0000000057f4dc900157f4ea9edd0000 VVV
00000000580065c5015800746d750000 CCC
00000000580065c501581d9f04f0000b TTT


And I want get this:

ID TITLE count
402884f657e0a6d20157e0a82cc90000 brother 2
402884f657e652fb0157e65642750000 BBB 0
00000000580065c501581d9f04f0000b TTT 0
402884f657e0a6d20157e11967a20036 fg 0
0000000057f4dc900157f4ea9edd0000 VVV 0
40284c8157ad8e7d0157ad8f86880000 1234567890123456789012345 0
402884f657e51eff0157e54cd8610004 AAA 0
00000000580065c5015800746d750000 CCC 0


so,I tried this and it worked:

SELECT t.ID,t.TITLE, COALESCE(r.c,0) `count`
FROM `test` t
LEFT OUTER JOIN
(
SELECT r.REVIEW_OBJ_ID obj_id, COUNT(r.ID) c
FROM review r,`test` t
WHERE r.REVIEW_TYPE = '4'
AND t.ID = r.REVIEW_OBJ_ID
) r ON r.obj_id = t.ID
ORDER BY `count` DESC;


But I have two questions:


  1. It feels I can use one-time select to found out result,but I use two-times select.Can I optimize it?

  2. Add a
    count
    (redundant) in the test table fields, whether it is a better choice.



/REVIEW_TYPE and REVIEW_OBJ_ID decide which object is reviewed,just like I use "REVIEW_TYPE='4'" to contact the
test
table/

drop table if exists user_doctor_review;
create table review
(
ID varchar(64) not null,
USER_ID varchar(64),
DOCTOR_ID varchar(64),
REVIEW_TYPE varchar(1),
REVIEW_OBJ_ID varchar(64),
SERVICE_SCORE int(6),
REVIEW_CONTENT varchar(600),
REVIEW_TIME datetime,
POID varchar(64),
IS_ANONYMITY varchar(1),
CHECKED_STATUS varchar(1),
STATUS varchar(1),
REPLY_CONTENT varchar(600),
REPLY_TIME datetime,
DOCTOR_IS_READ varchar(1),
primary key (ID)
);

Answer

Yes you can do it with one SELECT statement with this query

SELECT test.ID, test.TITLE, count(review.ID) as count from test
left join review on test.ID = review.REVIEW_OBJ_ID
where review.REVIEW_TYPE = 4 or review.ID is null
group by test.ID

I have created a SQL Fiddle here: http://sqlfiddle.com/#!9/c6a178/15

Explanation: The key points are:

or review.ID is null

because it will make the query list the tests with no reviews, and

group by test.ID

this will get the correct review counts related to the test.

Results:

ID                                  TITLE                       count
--------------------------------    -------------------------   -----
0000000057f4dc900157f4ea9edd0000    VVV                         0
00000000580065c5015800746d750000    CCC                         0
00000000580065c501581d9f04f0000b    TTT                         1
40284c8157ad8e7d0157ad8f86880000    1234567890123456789012345   0
402884f657e0a6d20157e0a82cc90000    brother                     2
402884f657e0a6d20157e11967a20036    fg                          0
402884f657e51eff0157e54cd8610004    AAA                         0
402884f657e652fb0157e65642750000    BBB                         0