benmoreassynt wrote:

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

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to