Have you tried limiting it first via a subselect and then doing the full
text match?
bastien
From: benmoreassynt <roland@xxxxxxxxxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx
Subject: 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/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
