Re: Speeding up a query by narrowing it down

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/