Full text-Query optimaliseren
- Topics
+ topicID
+ forumID (Topics vallen onder een bepaald forum)
+ firstPost (Ieder topic begint met 1 post, hiervan gebruik ik dus de subject
- Users
+ userID
+ naam
- Posts
+ postID
+ userID (Om een post aan een user te koppelen)
+ topicID (Een post valt uiteraard binnen een topic)
+ subject (Full-text)
+ content (Full-text)
+ datetime
De Query;
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT p.postID, p.topicID, u.userID, u.name, DATE_FORMAT( p.datetime, '%e %M %Y, %H:%i' ) AS newDatetime, (
SELECT subject
FROM icey_posts
WHERE postID = t.firstPost
) AS subject,
MATCH (
subject, content
)
AGAINST (
'hallo'
IN BOOLEAN
MODE
) AS score, sum( t.firstPost ) AS test
FROM icey_posts AS p, icey_topics AS t, icey_users AS u
WHERE MATCH (
subject, content
)
AGAINST (
'hallo'
IN BOOLEAN
MODE
)
AND p.topicID = t.topicID
AND p.userID = u.userID
GROUP BY t.firstPost
ORDER BY score DESC
SELECT subject
FROM icey_posts
WHERE postID = t.firstPost
) AS subject,
MATCH (
subject, content
)
AGAINST (
'hallo'
IN BOOLEAN
MODE
) AS score, sum( t.firstPost ) AS test
FROM icey_posts AS p, icey_topics AS t, icey_users AS u
WHERE MATCH (
subject, content
)
AGAINST (
'hallo'
IN BOOLEAN
MODE
)
AND p.topicID = t.topicID
AND p.userID = u.userID
GROUP BY t.firstPost
ORDER BY score DESC
Die sum( t.firstPost ) en GROUP BY gebruik ik omdat ik alle posts die gevonden worden binnen 1 topic bij elkaar wil hebben. Die subquery gebruik ik voor het ophalen van een subject.
Kan dit efficienter/sneller? Is het ook mogelijk om de scores van alle losse posts binnen 1 topic bij elkaar op te tellen?
Gewijzigd op 01/01/1970 01:00:00 door Arjan Kapteijn
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT p.postID, p.userID, p.topicID, u.name, t.subject, DATE_FORMAT( p.datetime, '%e %M %Y, %H:%i' ) AS newDatetime, ROUND( SUM(
MATCH (
content
)
AGAINST (
'1234'
) ) , 2 ) AS score
FROM icey_posts AS p, icey_users AS u, icey_topics AS t
WHERE MATCH (
content
)
AGAINST (
'1234'
IN BOOLEAN
MODE
)
AND p.userID = u.userID
AND p.topicID = t.topicID
GROUP BY p.topicID
ORDER BY score DESC , p.datetime DESC
LIMIT 25
MATCH (
content
)
AGAINST (
'1234'
) ) , 2 ) AS score
FROM icey_posts AS p, icey_users AS u, icey_topics AS t
WHERE MATCH (
content
)
AGAINST (
'1234'
IN BOOLEAN
MODE
)
AND p.userID = u.userID
AND p.topicID = t.topicID
GROUP BY p.topicID
ORDER BY score DESC , p.datetime DESC
LIMIT 25
Geeft heel acceptabele results terug dus hier ga ik nu even mee accoord. Als ik zo een gevulde database heb kijk ik wel verder.
