explain SELECT u.*,t.*,p.pic_url FROM `user` u LEFT JOIN `type` t ON u.`type_id`=t.`type_id` LEFT JOIN `pic` p ON u.`user_id`=p.`user_id` WHERE 1 AND u.`user_type`='2' AND u.`user_where`!='2' ORDER BY u.`user_id` DESC LIMIT 0 , 18 ; 你的语句,贴出来看一下。
mysql> explain -> SELECT u.*,t.*,p.* -> FROM `user` u -> LEFT JOIN `type` t ON u.`type_id`=t.`type_id` -> LEFT JOIN pic p ON u.user_id=p.user_id -> WHERE 1 AND u.`user_type`='2' AND u.`user_where`!='2' -> ORDER BY u.`user_id` DESC -> LIMIT 0 , 18 ; +----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 56492 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | huiyou.u.utype_id | 1 | | | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 47466 | | +----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+ 3 rows in set (0.02 sec)
如何查看错误日志,慢日志,在phpmyadmin中。
试一下这个SELECT u.*,t.*,p.* from (select * from `user` order by user_id desc LIMIT 0 , 18 ) u LEFT JOIN `type` t ON u.`type_id`=t.`type_id` LEFT JOIN pic p ON u.user_id=p.user_id WHERE 1 AND u.`user_type`='2' AND u.`user_where`!='2' ORDER BY u.`user_id` DESC LIMIT 0 , 18 ;
语义上应该没有问题。它只要前 18 条记录。并且是 left join 这样可以知道, 从 user 表中取的记录决不会超前 18 个user.这样从USER表中仅取18个记录,然后再做LEFT JOIN后再取前 18 个
错了,应该把u.`user_type`='2' AND u.`user_where`!='2'一起放入子查询,并且接受limit 18的约束。
比如pic.user_id肯定需要。
还有where里面的。
FROM `user` u
LEFT JOIN `type` t ON u.`type_id`=t.`type_id`
LEFT JOIN `pic` p ON u.`user_id`=p.`user_id`
WHERE 1 AND u.`user_type`='2' AND u.`user_where`!='2'
ORDER BY u.`user_id` DESC
LIMIT 0 , 18 ; 你的语句,贴出来看一下。
mysql> explain
-> SELECT u.*,t.*,p.*
-> FROM `user` u
-> LEFT JOIN `type` t ON u.`type_id`=t.`type_id`
-> LEFT JOIN pic p ON u.user_id=p.user_id
-> WHERE 1 AND u.`user_type`='2' AND u.`user_where`!='2'
-> ORDER BY u.`user_id` DESC
-> LIMIT 0 , 18 ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 56492 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | huiyou.u.utype_id | 1 | |
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 47466 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
3 rows in set (0.02 sec)
如何查看错误日志,慢日志,在phpmyadmin中。
from
(select * from `user` order by user_id desc LIMIT 0 , 18 ) u
LEFT JOIN `type` t ON u.`type_id`=t.`type_id`
LEFT JOIN pic p ON u.user_id=p.user_id
WHERE 1 AND u.`user_type`='2' AND u.`user_where`!='2'
ORDER BY u.`user_id` DESC
LIMIT 0 , 18 ;
你这个应该不行的吧,选出18条的里面符合 where 条件的才会出现。
不好意思,我 没看后来的回复。以及好了。3q。
还没睡啊。能帮我看下discuz 获取最新帖子的问题吗?(只已知一个fid,求最新帖子列表)
http://topic.csdn.net/u/20091219/00/ec37596a-5c6e-4ac8-a1ce-e0c4e5ac2325.html