Speeding up a query by narrowing it down

am 30.11.2006 03:02:03 von benmoreassynt

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

RE: Speeding up a query by narrowing it down

am 30.11.2006 03:42:57 von Bastien Koert

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

bastien

>From: benmoreassynt
>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 morethen 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

RE: Speeding up a query by narrowing it down

am 30.11.2006 04:45:37 von benmoreassynt

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
>>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 morethen 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

Re: Speeding up a query by narrowing it down

am 30.11.2006 05:11:27 von Chris

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/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Speeding up a query by narrowing it down

am 30.11.2006 05:19:06 von benmoreassynt

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
>>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 morethen 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

RE: Speeding up a query by narrowing it down

am 30.11.2006 05:20:36 von benmoreassynt

Re: Speeding up a query by narrowing it down

am 30.11.2006 05:23:49 von Chris

benmoreassynt wrote:
> 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`.

What indexes do you have on the table ?

Do a:

show indexes from table;

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Speeding up a query by narrowing it down

am 30.11.2006 05:42:26 von benmoreassynt

Index details:
contents 0 PRIMARY 1 id A 56 NULL NULL BTREE
contents 1 fulltextindex 1 fulltext NULL 1 NULL NULL YES FULLTEXT

I tried indexing all the fields separately and together (for example an
index that included fulltext, startdate and enddate). In fact more complex
indexing only slowed the searches down, or had no effect because MySQl
would always stick with the fulltext index.

The fields are

id
filename
fulltext
startdate
enddate
series
volume

Sorry for the multiple posts - my newsreader sucks for some reason.

Thanks for your help

R

Chris wrote:

> benmoreassynt wrote:
>> 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`.
>
> What indexes do you have on the table ?
>
> Do a:
>
> show indexes from table;
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Speeding up a query by narrowing it down

am 30.11.2006 06:02:42 von Chris

benmoreassynt wrote:
> Index details:
> contents 0 PRIMARY 1 id A 56 NULL NULL BTREE
> contents 1 fulltextindex 1 fulltext NULL 1 NULL NULL YES FULLTEXT
>
> I tried indexing all the fields separately and together (for example an
> index that included fulltext, startdate and enddate). In fact more complex
> indexing only slowed the searches down, or had no effect because MySQl
> would always stick with the fulltext index.
>
> The fields are
>
> id
> filename
> fulltext
> startdate
> enddate
> series
> volume

Hmm maybe try it as a self-join.

select id, blah from contents c1 inner join contents c2 using (id) where
c1.somefield < 1500 and c1.someotherfield > 1500 and match(c2.fulltext)
against ('search term');

No idea if that will work or produce right results though ;)

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php