benmoreassynt wrote:
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.
That's a mysql limitation. From what I've read mysql will only use one
index when it looks at a table (it picks up the one it thinks is right),
it won't use multiple indexes to limit results.
As Bastien suggested, a subquery might help:
select * from contents where id in (select id from contents where
match(fulltext) against(search string));
(Personally I'd also suggest renaming the "fulltext" column to something
that isn't a reserved word but that's not going to make any difference
here).
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
