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

Author

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

Bastien Koert

2006-11-29, 9:57 pm

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/

benmoreassynt

2006-11-30, 3:57 am

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
>
>
>
> ________________________________________
_________________________
> 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/

benmoreassynt

2006-11-30, 3:57 am

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
>
>
>
> ________________________________________
_________________________
> 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/

benmoreassynt

2006-11-30, 3:57 am

Chris

2006-11-30, 3:57 am

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/

benmoreassynt

2006-11-30, 3:57 am

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

Chris

2006-11-30, 3:57 am

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/

Sponsored Links