| benmoreassynt 2006-11-29, 9:57 pm |
| Hi,
I have a query which works successfully, but which has the potential to be
slow as the database grows.
The query is:
SELECT `id`, LOCATE('my search string', `fulltext`) FROM `contents` WHERE
MATCH (`fulltext`) AGAINST ('"my search string"' IN BOOLEAN MODE)
So it is getting the ID and location of a string in a table containing large
fulltext indexed entries.
Now, what seemed to me to be logical was that I could speed up the query by
adding lines like this:
AND somefield <= '1550'
AND anotherfield >= '1500'
So that MySQL would not bother looking at lines where `somefield` or
`anotherfield` were outside the ranges mentioned.
But instead MySQL seems to check all the lines for hits, and only THEN
narrows by using the other fields. So the query can actually be slower when
qualified more, and ORDER BY makes it slower still. I've tried all sorts of
indexes, but the fastest arrangement remains a fulltext index on
the 'fulltext' field.
Can anybody think of a way to deal with this so that MySQL doesn't waste
time searching where it doesn't need to?
Many thanks for you help
BMA
|