SELECT m.uid, m.username, m.gender, m.email, m.regdate, m.lastvisit, m.posts, m.credits, m.showemail, mf.nickname, mf.site, mf.location FROM cdb_members m LEFT JOIN cdb_memberfields mf USING(uid) ORDER BY uid LIMIT 0, 25; -----------------------and -----------------------SELECT m.uid, m.username, m.gender, m.email, m.regdate, m.lastvisit, m.posts, m.credits, m.showemail FROM cdb_members m order by m.uid LIMIT 0, 25;SELECT mf.uid,mf.nickname, mf.site, mf.location FROM cdb_memberfields mf ORDER BY mf.uid LIMIT 0, 25; -----------------------They are not the same. They won't return the same result.
SELECT m.uid, m.username, m.gender, m.email, m.regdate, m.lastvisit, m.posts, m.credits, m.showemail, mf.nickname, mf.site, mf.location FROM cdb_members m LEFT JOIN cdb_memberfields mf USING(uid) ORDER BY uid ----------------------------- It will return all the data from the first table (cdb_members), and the data from the second table (cdb_memberfields) which the uid of it also exists in the first table (cdb_members).
将之拆分为两条语句后可以得到同样的结果(自己合并一下)。------------------------------- How much time you spend to combine the two results ?
m.showemail, mf.nickname, mf.site, mf.location FROM cdb_members m
LEFT JOIN cdb_memberfields mf USING(uid) ORDER BY uid LIMIT 0, 25;
-----------------------and
-----------------------SELECT m.uid, m.username, m.gender, m.email, m.regdate, m.lastvisit, m.posts, m.credits, m.showemail FROM cdb_members m order by m.uid LIMIT 0, 25;SELECT mf.uid,mf.nickname, mf.site, mf.location FROM cdb_memberfields mf ORDER BY mf.uid LIMIT 0, 25;
-----------------------They are not the same. They won't return the same result.
m.showemail, mf.nickname, mf.site, mf.location FROM cdb_members m
LEFT JOIN cdb_memberfields mf USING(uid) ORDER BY uid -----------------------------
It will return all the data from the first table (cdb_members), and the data from the second table (cdb_memberfields) which the uid of it also exists in the first table (cdb_members).
How much time you spend to combine the two results ?
ob_start();
microtime();
执行sql
比较microtime();
显示执行时间经过测试,不论是分成2条,还是直接把order by 去掉都是执行0.003秒。但原样执行就是1秒左右。explain的结果table m mf
type index eq_ref
possible_keys NULL primary
key primary primary
key_len 3 3
ref NULL m.uid
rows 173378 1
Extra NULL NULL原样执行时间1秒左右。去掉order by后,table m的type变成all,这时执行时间只要0.003秒。