PHP / MySQLPosted on 2023-01-03
摘要 : 多个表格组合查询时,小数据时使用 id IN (id范围) 比 INNER JOIN 快。
大数据的话inner join更稳定
❱ 交叉查询两个表格
主表 post:包含id,title,summary,content 等
次表 link:包含 id,postid,catid
❱ 目的:对某些cat 分类下的文章进行关键词(我 有 可)的搜索
使用 INNER JOIN :共 499 行, 查询花费 0.0195 秒
SELECT * ,
(( CASE WHEN title LIKE '%我%' THEN 10 else 0 END ) + ( CASE WHEN summary LIKE '%我%' THEN 10-3 else 0 END ) + ( CASE WHEN title LIKE '%有%' THEN 9 else 0 END ) + ( CASE WHEN summary LIKE '%有%' THEN 9-3 else 0 END ) + ( CASE WHEN title LIKE '%可%' THEN 8 else 0 END ) + ( CASE WHEN summary LIKE '%可%' THEN 8-3 else 0 END ) )as score
FROM post use index(keywords)
INNER JOIN ( SELECT DISTINCT postid FROM link use index(keywords) WHERE catid IN (1,2,3) )as getids
ON post.id = getids.postid
WHERE enable=1
AND ( ( title LIKE '%我%' OR summary LIKE '%我%' )
OR ( title LIKE '%有%' OR summary LIKE '%有%' )
OR ( title LIKE '%可%' OR summary LIKE '%可%' ) )
AND (create_date BETWEEN '2000' AND '2020-12-12')
ORDER BY score DESC
//LIMIT 40 , 20
使用 id IN(id范围) :共 499 行, 查询花费 0.0128 秒
SELECT * ,
(( CASE WHEN title LIKE '%我%' THEN 10 else 0 END ) + ( CASE WHEN summary LIKE '%我%' THEN 10-3 else 0 END ) + ( CASE WHEN title LIKE '%有%' THEN 9 else 0 END ) + ( CASE WHEN summary LIKE '%有%' THEN 9-3 else 0 END ) + ( CASE WHEN title LIKE '%可%' THEN 8 else 0 END ) + ( CASE WHEN summary LIKE '%可%' THEN 8-3 else 0 END ) )as score
FROM post use index(keywords)
WHERE
id IN ( SELECT DISTINCT postid FROM link use index(keywords) WHERE catid IN (1,2,3) )
AND enable=1
AND ( ( title LIKE '%我%' OR summary LIKE '%我%' )
OR ( title LIKE '%有%' OR summary LIKE '%有%' )
OR ( title LIKE '%可%' OR summary LIKE '%可%' ) )
AND (create_date BETWEEN '2000' AND '2020-12-12')
ORDER BY score DESC
//LIMIT 40 , 20
❱ 使用 FULLTEXT + 中文base64转码
使用 INNER JOIN ://共 382 行, 查询花费 0.0132 秒。[score: 8.941804885864258... - 3.942620277404785...]
SELECT * , MATCH (keywords) AGAINST ('5aW9 5aSa 5Y+v 5Lul 6Zi/ 5pav 6aG/ css jQuery ajax' IN NATURAL LANGUAGE MODE)as score
FROM post use index(keywords)
INNER JOIN ( SELECT DISTINCT postid FROM link use index(keywords) WHERE catid IN (1,2,3,4,5,6) )as getids
ON post.id = getids.postid
WHERE enable=1
AND MATCH (keywords) AGAINST ('5aW9 5aSa 5Y+v 5Lul 6Zi/ 5pav 6aG/ css jQuery ajax' IN NATURAL LANGUAGE MODE)
AND (create_date BETWEEN '2000' AND '2020-12-12')
ORDER BY score DESC
//LIMIT 40 , 20
使用 id IN(id范围) :共 382 行, 查询花费 0.0082 秒。) [score: 8.941804885864258... - 3.942620277404785...
SELECT * , MATCH (keywords) AGAINST ('5aW9 5aSa 5Y+v 5Lul 6Zi/ 5pav 6aG/ css jQuery ajax' IN NATURAL LANGUAGE MODE)as score
FROM post use index(keywords)
WHERE enable=1
AND id IN ( SELECT DISTINCT postid FROM link use index(keywords) WHERE catid IN (1,2,3,4,5,6) )
AND MATCH (keywords) AGAINST ('5aW9 5aSa 5Y+v 5Lul 6Zi/ 5pav 6aG/ css jQuery ajax' IN NATURAL LANGUAGE MODE)
AND (create_date BETWEEN '2000' AND '2020-12-12')
ORDER BY score DESC
//LIMIT 40 , 20