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
