RE: [PHP-DB] Speeding up a query by narrowing it down

Have you tried limiting it first via a subselect and then doing the full text match? bastien

From: benmoreassynt <roland@tannerritchie.com> To: php-db@lists.php.net Subject: [PHP-DB] Speeding up a query by narrowing it down Date: Wed, 29 Nov 2006 21:02:03 -0500 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

_________________________________________________________________ Find a local pizza place, music store, museum and more…then map the best route! Check out Live Local today! http://local.live.com/?mkt=en-ca/