I think the problem may stem from the presence of the FULLTEXT index itself.
Every time there is a query involving a FULLTEXT index, the MySQL Query Optimizer tends to whack the query into a full table scan. I have seen this over the years. I also wrote an earlier post about this most trifling behavior in FULLTEXT indexes.
You may need to do two things:
- refactor the query so that the FULLTEXT index does not throw the MySQL Query Optimizer into a state of confusion
- Add an additional index that will properly support the refactored query
REFACTOR THE QUERY
Here is your original query
SELECT post_id
FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
AND MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')
You will need to refactor the query like this:
SELECT subqueryA.post_id
FROM
(
SELECT post_id FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
) subqueryA
INNER JOIN
(
SELECT post_id FROM phpbb_posts
WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar')
) subqueryB
USING (post_id);
CREATE A NEW INDEX
You will need an index to support subqueryA
. You already have an index on topic_id
. You need to replace it as follows:
ALTER TABLE phpbb_posts ADD INDEX topic_post_ndx (topic_id,post_id);
ALTER TABLE phpbb_posts DROP INDEX topic_id;
Give it a Try !!!
UPDATE 2012-03-19 13:08 EDT
Try this one first
SELECT post_id FROM
(
SELECT * FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
) A;
If this runs fast and return a small number of rows, then try this nested subquery:
SELECT post_id FROM
(
SELECT * FROM phpbb_posts
WHERE topic_id = 144017
AND post_id != 155352
) A
WHERE MATCH(post_text) AGAINST('http://rapidshare.com/files/5494794/photo.rar');
UPDATE 2012-03-19 13:11 EDT
Compare the running time of this:
SELECT count(*) FROM phpbb_posts WHERE MATCH(post_text) AGAINST ('rapidshare.com') LIMIT 0, 30;
with this
SELECT count(*) FROM phpbb_posts WHERE 1 = 1;
If there running time is the same, then the MATCH clause is being executed on every row. As I mentioend earlier, using FULLTEXT indexes tends to nullify any benefits attempted and contributed by the MySQL Query Optimizer.
I suspect (with a high degree of of confidence) that the /
isn't considered a "word" character. If that is the case, then Fine Tuning MySQL Full Text Search in the manual explains how to accomplish what you're wanting to do.
If you want to change the set of characters that are considered word characters, you can do so in several ways, as described in the following list. After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes. Suppose that you want to treat the hyphen character ('-') as a word character. Use one of these methods...
The second option offered there is probably the simplest, since you can apparently just edit the Character Definition Array in the appropriate XML file -- assuming you can use a one-byte character set such as latin1
for this column of this table. If you need utf8
or anything else multibyte, it apparently means recompiling the server from source after customizing the source to your specific needs.
Best Answer
FULLTEXT
andBTree
cannot be mixed in a single composite index.Please provide
SHOW CREATE TABLE
and theSELECT
for further discussion of alternatives.Always(?), the Optimizer will pick a
FULLTEXT
index over any other. There are a few cases where complicating the query with a subquery can make it run faster. (But let's see what you are doing.)