日期:2014-05-16 浏览次数:20838 次
EXPLAIN SELECT * FROM table1 INNER JOIN table2 ON table1.pid = table2.pid LEFT JOIN table3 ON table1.pid = table3.pid WHERE MATCH ( table3.title, table3.content ) AGAINST ( '+words' IN BOOLEAN MODE ) ORDER BY table3.pid
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table1 ALL pid NULL NULL NULL 201497 Using temporary; Using filesort 1 SIMPLE table2 ref pid pid 32 mydb.table1.pid 1 1 SIMPLE table3 ref pid pid 32 mydb.table2.pid 222309 Using where
SELECT * FROM (SELECT table1.id as id1 FROM table1 INNER JOIN table2 ON table1.id = table2.id) t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(table1.id) FROM table1)) AS id2) t2 WHERE t1.id1 >= t2.id2 LIMIT 1
------解决方案--------------------
SELECT *
FROM table1
INNER JOIN table2 ON table1.pid = table2.pid
这样的速度呢?实在不行,就拆开来写吧。
------解决方案--------------------
呃.left join 和不用的效果一样只存在概率事件。就看你具体表及字段的设计方式了。
left join :以左表为基础,从右表找出on关键字相同的值进行匹配
from a,b呢,不存在匹配,取两个表的记录乘积。
------解决方案--------------------
SELECT *
FROM table1
USE INDEX(PRIMARY) JOIN table2 ON table1.pid = table2.pid
这样试试看