3表联合查询,速度慢。
下面是EXPLAIN的结果
table1 为 myisam, table2,table3为innodb,table1已经加了fulltext,奇怪的是为什么EXPLAIN后看不见?3个表的pid都加了index。可是现在的查询速度慢的像蜗牛(10几秒)。帮忙看下问题在哪里?谢谢。
EXPLAIN SELECT * FROM table1INNER JOIN table2 ON table1.pid = table2.pidLEFT JOIN table3 ON table1.pid = table3.pidWHERE MATCH (table3.title, table3.content)AGAINST ('+words'IN BOOLEANMODE)ORDER BY table3.pid
id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE table1 ALL pid NULL NULL NULL 201497 Using temporary; Using filesort1 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) t1JOIN (SELECT ROUND(RAND() * (SELECT MAX(table1.id) FROM table1)) AS id2) t2 WHERE t1.id1 >= t2.id2LIMIT 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
这样试试看