select * from table3 WHERE MATCH ( title, content ) AGAINST ( '+words' IN BOOLEAN MODE ) ORDER BY pid 0.04 seconds. 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 16.87 seconds.单独运行和联合运行,速度相差40倍。
另外:SELECT * FROM (SELECT table1.id as id1 FROM table1 INNER JOIN table2 ON table1.id = table2.id) as t1 JOIN ( (SELECT ROUND(RAND() * (SELECT MAX(table1.id) FROM table1)) AS id2) AS t2 ) WHERE t1.id1 >= t2.id2 LIMIT 1报错,#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(table1.id) FROM table1))' at line 1什么原因?
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, 速度很快只需0.12秒。 试了下结果好像一样,帮忙看看是不是这一会事?select * from table1,table2,table3 where table1.pid = table2.pid and table1.pid = table3.pid and MATCH ( table3.title, table3.content ) AGAINST ( '+words' IN BOOLEAN MODE ) ORDER BY table3.pid PS: CSDN小结。 发一个贴-40分。 第一天发帖提问+10分,第二天回帖鸣谢+10分,结贴+20分。 变相的永远不会被扣分……
呃.left join 和不用的效果一样只存在概率事件。就看你具体表及字段的设计方式了。 left join :以左表为基础,从右表找出on关键字相同的值进行匹配 from a,b呢,不存在匹配,取两个表的记录乘积。
SELECT * FROM table1 USE INDEX(PRIMARY) JOIN table2 ON table1.pid = table2.pid 这样试试看
WHERE MATCH (
title, content
)
AGAINST (
'+words'
IN BOOLEAN
MODE
) ORDER BY pid
0.04 seconds.
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
16.87 seconds.单独运行和联合运行,速度相差40倍。
(SELECT table1.id as id1
FROM table1
INNER JOIN table2
ON table1.id = table2.id) as t1
JOIN (
(SELECT ROUND(RAND() * (SELECT MAX(table1.id) FROM table1)) AS id2)
AS t2 )
WHERE t1.id1 >= t2.id2
LIMIT 1报错,#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(table1.id) FROM table1))' at line 1什么原因?
(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
这样试试。
FROM table1
INNER JOIN table2 ON table1.pid = table2.pid
这样的速度呢?实在不行,就拆开来写吧。
试了下结果好像一样,帮忙看看是不是这一会事?select * from table1,table2,table3
where table1.pid = table2.pid
and table1.pid = table3.pid
and MATCH (
table3.title, table3.content
)
AGAINST (
'+words'
IN BOOLEAN
MODE
)
ORDER BY table3.pid
PS: CSDN小结。 发一个贴-40分。
第一天发帖提问+10分,第二天回帖鸣谢+10分,结贴+20分。
变相的永远不会被扣分……
left join :以左表为基础,从右表找出on关键字相同的值进行匹配
from a,b呢,不存在匹配,取两个表的记录乘积。
FROM table1
USE INDEX(PRIMARY) JOIN table2 ON table1.pid = table2.pid
这样试试看