Query needs Optimization

I'll start by saying that I am the furthest thing from a database admin... my knowledge is SQL queries is basic at best, which is why I'm asking for help here.

I am attempting to build a "search" query on a client's message board (running vBulletin). The query I've written works fine - with the exception that it's terribly slow (if I set the start/limit params to 0 an 25, the query takes upwards of 30-60 seconds).

Here's my query (a mix of PHP and raw SQL):

$totalQuery = '
SELECT DISTINCT
thread.threadid,
thread.title,
thread.lastpost,
thread.lastposter,
thread.forumid,
thread.postusername,
thread.postuserid,
thread.dateline
FROM thread
RIGHT JOIN
(
SELECT
post.threadid,
post.pagetext
FROM post
) AS post
ON thread.threadid = post.threadid
WHERE thread.forumid NOT IN
(
SELECT forum.forumid
FROM forum, forumpermission
WHERE FIND_IN_SET(forumpermission.forumid, forum.parentlist)
AND forumpermission.usergroupid >= "' . $_SESSION['perms']['permissions']->usergroupid . '"
)
AND
(
thread.title LIKE "%' . mysqli_real_escape_string($mysql, $args['querystring']) . '%"
OR post.pagetext LIKE "%' . mysqli_real_escape_string($mysql, $args['querystring']) . '%"
)
ORDER BY lastpost DESC
LIMIT ' . $start . ',' . $limit;

So the idea is that I'm searching through both the post TITLE and post CONTENT for anything close to the provided query. However, I also need to make sure that the user only gets results that they have permissions to see... I imagine this probably causes the problem.

There has to be a better way to optimize the query... all suggestions will be appreciated.

This things are the same forinstance



RIGHT JOIN
(
SELECT
post.threadid,
post.pagetext
FROM post
) AS post
ON thread.threadid = post.threadid




RIGHT JOIN post ON thread.threadid = post.threadid


, the main thing then, that is slowing things down is, mybe you are not using indexes at all and use of '%.....%'

to test my statement about like try to exclude


AND
(
thread.title LIKE "%' . mysqli_real_escape_string($mysql, $args['querystring']) . '%"
OR post.pagetext LIKE "%' . mysqli_real_escape_string($mysql, $args['querystring']) . '%"
)


from sql and see the timing neded

Ok, thanks for the response.

That bit offers a slight improvement, so I'll make the change.

However, I believe I need to do some sort of OUTER JOIN rather than the NOT IN statement... but I can't make sense of it. Any ideas?

join forumpermission
on thread.forumid = forumpermission.forumid and
not FIND_IN_SET(forumpermission.forumid, forum.parentlist)
AND forumpermission.usergroupid >= "' . $_SESSION['perms']['permissions']->usergroupid .


having no tables struct, something close to that

red part could go also into where part of sql then

Your RIGHT JOIN has also been converted into an INNER JOIN whether you knew it or not.

Look here:
http://www.codingforums.com/showthread.php?p=818192#post818192

Since you are doing FROM thread RIGHT JOIN ...
in this case thread is the dependent table and so any use of a WHERE clause on it means the RIGHT JOIN is transformed into an INNER JOIN.

This may or may not matter for the results you want. You will have to decide.

Thanks for the reply. The RIGHT JOIN (INNER JOIN) is fine, but I appreciate you pointing that out.

I'm still working on the correct syntax for that JOIN statement... but I'm getting there. Hopefully by the end of today I'll have it figured out.

What I've got so far:

SELECT DISTINCT
thread.threadid,
thread.title,
thread.lastpost,
thread.lastposter,
thread.forumid,
thread.postusername,
thread.postuserid,
thread.dateline
FROM thread
INNER JOIN post
ON thread.threadid = post.threadid

INNER JOIN forum
ON thread.forumid = forum.forumid

RIGHT JOIN forumpermission
ON NOT FIND_IN_SET(forumpermission.forumid, forum.parentlist)

AND forumpermission.usergroupid >= "0"

WHERE

thread.title LIKE "%test%"
OR post.pagetext LIKE "%test%"

ORDER BY lastpost DESC
LIMIT 0,25;

Currently the JOIN on forumpermission doesn't return what I want it to, but I figure it may just be a matter of correcting the ON clause.