Have you tried limiting it first via a subselect and then doing the full
text match?
bastien
From: benmoreassynt [EMAIL PROTECTED]
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
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 [EMAIL PROTECTED]
To:
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
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` =
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` =
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`
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
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
