Yeah, I had thought of that, but had not got far with it. I'll have another
go as I was getting errors last time.
Bastien Koert wrote:
> 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
