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 can provide with a general explanation, but it may not apply specifically to your particular case:
The way decision making works is by evaluation cost of execution plan, then picking up what is hopefully the cheapest plan. This you already know.
When it comes to indexing, though, stuff are getting interesting. The way to evaluate the usefulness or viability of an index is to estimate the selectivity given some value.
For the moment, forget about your FULLTEXT index, and let's assume a simple index on some column col1
, and another index on some column col2
. Given the following two queries:
SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;
It may happen that the query is evaluated differently in these two cases. Why? Because it may happen that col2 = 4
returns more rows than col1 < 10
, in which case we prefer to use index on col1
. But then, it may return less rows than col1 BETWEEN 100 AND 110
, in which case we prefer the index on col2
.
Your case is not very much different. MySQL estimates the number of rows returned by some index query. When you use more columns, MySQL gets the impression your index is likely to result with few rows. So it chooses to start with TableA
, then joins what should be very few rows with TableB
.
But if MySQL believes the index to return many rows, it may prefer starting with TableB
. Why is that? Because you are sorting on indexed columns of TableB
. Sorting is a lot of work, too. So MySQL may choose to first sort the rows, then join to TableA
and filter by fulltext index. It may not be a bad idea if the fulltext search yields with many rows anyhow.
Best Answer
I don't know the answer; let me try to figure it out. First, I don't see anything likely in
information_schema
ormysql
. In the filesystem, in the directory with same name as the database, look for files starting withFTS_
. If you have multiple tables withFULLTEXT
indexes, segregate them by prefix, then sum up the sizes. For one table, I seeSHOW CREATE TABLE fti
mysql> SHOW TABLE STATUS LIKE 'fti'\G
ls -l FTS*
(blank lines added)The files add up to about 340MB for the largest index (
title
+msg
).The total text size (
SUM(LENGTH...
) is 220MB; 310MB is about right once you add on the off-record storage overhead. It seems reasonable for the FT index for a text column to be about the same bulk as the text.