+----+--------------------+-----------+-------+---------------+---------+-------
--+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_le
n | ref | rows | Extra |
+----+--------------------+-----------+-------+---------------+---------+-------
--+------+------+----------------------------------------------+
| 1 | PRIMARY | item | index | NULL | PRIMARY | 4
| NULL | 155 | Using where |
| 2 | DEPENDENT SUBQUERY | item | ALL | NULL | NULL | NULL
| NULL | 155 | Using where; Using temporary; Using filesort |
| 3 | DEPENDENT SUBQUERY | dest_user | ALL | NULL | NULL | NULL
| NULL | 469 | Using where |
+----+--------------------+-----------+-------+---------------+---------+-------
--+------+------+----------------------------------------------+
SELECT item.id, item.guid, item.parent, item.number, item.item_type,
item.title, item.title_sub, item.summary, item.summary_sub, item.keyword,
item.running_title, item.description, item.date, item.posted, item.accepted,
item.registrant_id, item.journal_id, item.status, item.result, item.version
FROM item WHERE item.id IN
(SELECT item.id FROM item WHERE EXISTS (SELECT item.id FROM dest_user
WHERE (((dest_user.user_id) = (2)) AND ((dest_user.item_id) = (item.id))))
GROUP BY item.guid) ORDER BY item.id DESC请教高手帮我优化一下,主要问题出在 EXISTS 这个关键字的查询上,能不能用左连接查询代替.如果可以请高手帮我改一下,万分感激
--+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_le
n | ref | rows | Extra |
+----+--------------------+-----------+-------+---------------+---------+-------
--+------+------+----------------------------------------------+
| 1 | PRIMARY | item | index | NULL | PRIMARY | 4
| NULL | 155 | Using where |
| 2 | DEPENDENT SUBQUERY | item | ALL | NULL | NULL | NULL
| NULL | 155 | Using where; Using temporary; Using filesort |
| 3 | DEPENDENT SUBQUERY | dest_user | ALL | NULL | NULL | NULL
| NULL | 469 | Using where |
+----+--------------------+-----------+-------+---------------+---------+-------
--+------+------+----------------------------------------------+
SELECT item.id, item.guid, item.parent, item.number, item.item_type,
item.title, item.title_sub, item.summary, item.summary_sub, item.keyword,
item.running_title, item.description, item.date, item.posted, item.accepted,
item.registrant_id, item.journal_id, item.status, item.result, item.version
FROM item WHERE item.id IN
(SELECT item.id FROM item WHERE EXISTS (SELECT item.id FROM dest_user
WHERE (((dest_user.user_id) = (2)) AND ((dest_user.item_id) = (item.id))))
GROUP BY item.guid) ORDER BY item.id DESC请教高手帮我优化一下,主要问题出在 EXISTS 这个关键字的查询上,能不能用左连接查询代替.如果可以请高手帮我改一下,万分感激
从item表中找出由item.guid分组后的item.id集合 (说明:这里分组的意思就是取有相同item.guid的一组记录中的item.id最大的那条记录)
再找出符合(dest_user.item_id) = (item.id)的item.id交集,
再从这个交集中找出对应的item表中记录,并进行排序.
(说明:item.id是唯一的,item.guid不是唯一的)继续请高手指点
2、TRY:
SELECT item.id, item.guid, item.parent, item.number, item.item_type,
item.title, item.title_sub, item.summary, item.summary_sub, item.keyword,
item.running_title, item.description, item.date, item.posted, item.accepted,
item.registrant_id, item.journal_id, item.status, item.result, item.version
FROM item WHERE item.id IN
(SELECT item.id FROM item WHERE EXISTS (SELECT item.id FROM dest_user
WHERE (((dest_user.user_id) = (2)) AND ((dest_user.item_id) = (item.id))))
GROUP BY item.guid) ORDER BY item.id DESC
SELECT a.id FROM item a inner join dest_user b on b.user_id='2' and a.item_id=b.item_id
GROUP BY a.guid
item.title, item.title_sub, item.summary, item.summary_sub, item.keyword,
item.running_title, item.description, item.date, item.posted, item.accepted,
item.registrant_id, item.journal_id, item.status, item.result, item.version
FROM item INNER JOIN
(SELECT a.id FROM item a inner join dest_user b on b.user_id='2' and a.item_id=b.item_id
GROUP BY a.guid ) B1
ON item.id=B1.ID
ORDER BY item.id DESC
不过还想再提个问题,谁能把上面的sql文转化为sqlobject格式的写法啊.