Re: reducing number of ANDs speeds up query

Hi and thank you!

On 12.01.2013, at 11:52, Eduardo Morras <emorrasg(at)yahoo(dot)es> wrote:

>> With the last "AND b.value=..." the query is extremely slow (did not wait for it to end, but more than a minute), because the value column is not indexed (contains items longer than 8K).
>
> You can construct your own home made index, add a new column in table b, with the first 8-16 bytes/chars of b.value, use this column on your query and refine to a complete b.value. Don't forget tocCreate an index for it too. You can keep this column updated with a trigger.

Yes, I have been considering this in a slightly different way. value contains short and long values (mixed). Only the short values are queried directly. The long values are queried in a tsearch column or in an external Sphinx Search. So probably I should split the short and long values and then index the short values.

It is nevertheless slightly annoying that I cannot make the query do the value thing last...

> Perhaps you can use a partial index for b.value column, i never used that feature so documentation/others can point you how to do it.

Did not know of them, reading. Thank you!
T.