Re: Left Join Not Using Index?

On Tue, 22 Apr 2003, Hunter Hillegas wrote:

> Your suggestion didn't really make a whole lot of sense to me... Based on
> this info, what do you think?

I was wondering if something like (columns removed because I'd go insane
otherwise, but I think this illustrates it):

select message_board_topics.rec_num from
message_board_topics where upper(topic_name) LIKE upper('madbrowser')
union
select message_board_topics.rec_num from
message_board_topics where upper(topic_body) LIKE upper('madbrowser')
union
select message_board_topics.rec_num from
message_board_topics where upper(topic_author) LIKE upper('madbrowser')
union
select message_board_topics.rec_num from
message_board_topics,message_board_comments where
message_board_comments.topic_id=message_board_topics.rec_num
and upper(message_board_comments.comment_author) LIKE upper('madbrowser')
order by 1 desc;

with indexes on upper(topic_name), upper(topic_body), etc... was
both the same and faster.

However, the best solution is probably some sort of full text indexing
solution. Putting the keywords from the various columns you want to index
along with the rec_num (or topic_id) of the row and an index on the text.
Then you could join message_board_topics with that and probably get a much
better plan.