Yeah, I had thought of a subquery, but had not got far with it.
I just edited the query to this:
SELECT `id`, LOCATE('my search text', `fulltext`) FROM `contents` WHERE
MATCH (`fulltext`) AGAINST ('"my search text"' IN BOOLEAN MODE) AND `id` IN
(SELECT `id` FROM `contents` WHERE `somefield` <= "1600" AND
`someotherfield` >= "1500")
The time for the query to process seems to be pretty much exactly the same,
and also the same if I omit the nested query entirely (in other words, not
limiting by `somefield` and `someotherfield`.
I also tried changing the order of queries in the WHERE clause. No effect.
Seems dumb that MySQL is wasting time on stuff it does not need to look at.
I guess I could do two totally separate queries - get the ids from a limited
number of lines, and then search them, but that seems to go against the
whole theory of queries.
Any ideas?
Cheers
Roland
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
