Re: reducing number of ANDs speeds up query

Hi and thank you!

On 12.01.2013, at 11:52, Eduardo Morras <emorrasg@yahoo.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),
becausethe 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,
usethis column on your query and refine to a complete b.value. Don't forget tocCreate an index for it too. You can keep
thiscolumn updated with a trigger. 

Yes, I have been considering this in a slightly different way. value contains short and long values (mixed). Only the
shortvalues are queried directly. The long values are queried in a tsearch column or in an external Sphinx Search. So
probablyI 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
youhow to do it. 

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