what mysql query is better?
i got 2 mysql queries, that retrieve the same data?, what one do you think is better to use and why? taking into account standards, and better code etc. sorry if this is a stupid question, but im a curious cat! so 开发者_如何学Pythonhere goes:
QUERY 1:
SELECT *
FROM
((
SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m.dt
FROM relationships r
JOIN notes m ON m.user_id = r.leader
JOIN user u ON r.leader = u.user_id
WHERE r.listener ='2'
)
UNION ALL
(
SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m.dt
FROM notes m
JOIN user u ON m.user_id = u.user_id
WHERE u.user_id ='2'
)) d
WHERE d.dt < '2010-09-20_131830'
ORDER BY d.dt DESC
QUERY 2:
SELECT u.username, u.picture, m.id, m.user_note, m.reply_id, m.reply_name, m.dt
FROM notes m
INNER JOIN user u
ON m.user_id = u.user_id
WHERE (m.user_id = '2'
OR m.user_id IN (
SELECT r.leader
FROM relationships r
WHERE r.listener ='2'))
AND dt < '2010-09-20_131830'
ORDER BY dt DESC
I find the UNION ALL
version much easier to understand (that could be me offcourse) but I'd rewrite it as below.
Adding the AND m.dt < '2010-09-20_131830
to each part should give you a better performance.
You could run it a few times and verify if it makes a difference.
SELECT u.username
, u.picture
, m.id
, m.user_note
, m.reply_id
, m.reply_name
, m.dt
FROM relationships r
INNER JOIN notes m ON m.user_id = r.leader
INNER JOIN user u ON r.leader = u.user_id
WHERE r.listener ='2'
AND m.dt < '2010-09-20_131830'
UNION ALL
SELECT u.username
, u.picture
, m.id
, m.user_note
, m.reply_id
, m.reply_name
, m.dt
FROM notes m
INNER JOIN user u ON m.user_id = u.user_id
WHERE u.user_id ='2'
AND m.dt < '2010-09-20_131830'
ORDER BY m.dt DESC
精彩评论