I have the following query:
set likepobeyintersects=1;
select story_id from story where headline\text likep 'mutual fund stock economy @0' order by arrival_time desc;
This works fine, and I get results back in under a second. However, when I add more conditions to the query, it slows down to the point where it takes 80 seconds, which is unusable for me:
set likepobeyintersects=1;
select story_id from story where (headline\text likep 'mutual fund stock economy @0') and (ip_id=1 OR ip_id=3 OR ip_id=9 OR ip_id=10 OR ip_id=10 OR ip_id=11 OR ip_id=12 OR ip_id=16 OR ip_id=22 OR ip_id=24 OR ip_id=2040 OR ip_id=27 OR ip_id=3001 OR ip_id=35 OR ip_id=10015 OR ip_id=10044 OR ip_id=10045 OR ip_id=10052 OR ip_id=10100 OR ip_id=10126 OR ip_id=10127 OR ip_id=10131 OR ip_id=10137 OR ip_id=10142 OR ip_id=10187 OR ip_id=10255 OR ip_id=10260 OR ip_id=10277 OR ip_id=10286 OR ip_id=10290 OR ip_id=10292 OR ip_id=10294 OR ip_id=10295 OR ip_id=10313 OR ip_id=10323 OR ip_id=10331 OR ip_id=10362 OR ip_id=10374 OR ip_id=10381 OR ip_id=10389 OR ip_id=10395 OR ip_id=10431 OR ip_id=10436 OR ip_id=10463 OR ip_id=10491 OR ip_id=10497 OR ip_id=10512 OR ip_id=10687 OR ip_id=10695 OR ip_id=10700 OR ip_id=10728 OR ip_id=11153 OR ip_id=11237 OR ip_id=11307 OR ip_id=11357 OR ip_id=11384 OR ip_id=11390 OR ip_id=11428 OR ip_id=11448 OR ip_id=11460 OR ip_id=11462 OR ip_id=11500 OR ip_id=11509) order by arrival_time desc;
There is an inverted metamorph index on the fields that might appear in the query:
create metamorph inverted index story_headline_text_idx on story(headline\text, ip_id, arrival_time, source, publication_date, categories, companies);
but that index doesn't seem to help. I tried speeding up the query by adding another condition - '$rank>400' - but that didn't help either, and the query is still too slow to be usable. According to 'tsql -V', the 'story_headline_text_idx' is the only index being used for either query. The table is large (2G), and has about 600,000 rows. The box is a Sun 420R with 4G or RAM.
Is there any way to speed up that query?
TIA,
-Gary
set likepobeyintersects=1;
select story_id from story where headline\text likep 'mutual fund stock economy @0' order by arrival_time desc;
This works fine, and I get results back in under a second. However, when I add more conditions to the query, it slows down to the point where it takes 80 seconds, which is unusable for me:
set likepobeyintersects=1;
select story_id from story where (headline\text likep 'mutual fund stock economy @0') and (ip_id=1 OR ip_id=3 OR ip_id=9 OR ip_id=10 OR ip_id=10 OR ip_id=11 OR ip_id=12 OR ip_id=16 OR ip_id=22 OR ip_id=24 OR ip_id=2040 OR ip_id=27 OR ip_id=3001 OR ip_id=35 OR ip_id=10015 OR ip_id=10044 OR ip_id=10045 OR ip_id=10052 OR ip_id=10100 OR ip_id=10126 OR ip_id=10127 OR ip_id=10131 OR ip_id=10137 OR ip_id=10142 OR ip_id=10187 OR ip_id=10255 OR ip_id=10260 OR ip_id=10277 OR ip_id=10286 OR ip_id=10290 OR ip_id=10292 OR ip_id=10294 OR ip_id=10295 OR ip_id=10313 OR ip_id=10323 OR ip_id=10331 OR ip_id=10362 OR ip_id=10374 OR ip_id=10381 OR ip_id=10389 OR ip_id=10395 OR ip_id=10431 OR ip_id=10436 OR ip_id=10463 OR ip_id=10491 OR ip_id=10497 OR ip_id=10512 OR ip_id=10687 OR ip_id=10695 OR ip_id=10700 OR ip_id=10728 OR ip_id=11153 OR ip_id=11237 OR ip_id=11307 OR ip_id=11357 OR ip_id=11384 OR ip_id=11390 OR ip_id=11428 OR ip_id=11448 OR ip_id=11460 OR ip_id=11462 OR ip_id=11500 OR ip_id=11509) order by arrival_time desc;
There is an inverted metamorph index on the fields that might appear in the query:
create metamorph inverted index story_headline_text_idx on story(headline\text, ip_id, arrival_time, source, publication_date, categories, companies);
but that index doesn't seem to help. I tried speeding up the query by adding another condition - '$rank>400' - but that didn't help either, and the query is still too slow to be usable. According to 'tsql -V', the 'story_headline_text_idx' is the only index being used for either query. The table is large (2G), and has about 600,000 rows. The box is a Sun 420R with 4G or RAM.
Is there any way to speed up that query?
TIA,
-Gary