Multiple Indexes per Table

[aitrus]

I ran into an issue and decided to experiment with having multiple
indexes in one table--that I could use with thinking sphinx. When
doing generic searches, I don't want case sensitivity to be an issue.
I also want to be able to do partial searches, using asterisks.

However, I also want to do case-sensitive searches against the full,
literal value of a column. So, "AbCD*" would only match a search of
"AbCD*" and not "AbCD" or "AbC*", etc.

Inside my model, if I do two define_index 's, It creates two sources
(as seen below in my pastie). However, it only creates one
index ..._core. So if I set any conflicting property values in the
define indexes, it will only use one of them.

In addition, if the two define_index 's do not have the same number of
records / attributes / etc, Sphinx will error out when trying to index
them due to a mismatch. Soo... I half fixed the issue below, by semi-
customizing that section in the sphinx.conf. See here:

http://pastie.org/479699

I had to create two "index ..._core_XX" entries and told the index to
point at both. This lets me have two sets of files with a different
number of attributes and different properties.

One issue, however is now this:

>> GeneralResource.search :conditions => {:profile_name_lit => "TSTCERT.xfgyhd"}, :limit => 1
=> [nil]

This doesn't happen with every value. I'm still doing some testing to
see why (exactly) it will behave like this.

But... I think supporting this would be a very worthwhile change to
Thinking Sphinx.

[aitrus]

I'll look at how difficult it might be to add these changes into
TSphinx, as well. They're pretty much invaluable.

[Pat Allan]

I'd like multiple define_index calls to define multiple indexes. I've
just rolled out some changes that lets people define multiple sources
within a define_index block (although this is the first time I've
mentioned it):
define_index do
define_source do
# ...
end
end

If define_source isn't called (as per pretty much everyone's config
currently), it just creates a default source.

I've not made any changes for define_index to now work reliably for
multiple indexes though. If you want to tackle that, please do. I've
been considering it as a way to allow multiple language translations
of a record. I'm not sure how Sphinx handles the same document across
multiple indexes though. Something work clarifying on the Sphinx
forum, perhaps.

Cheers

--
Pat

[aitrus]

I think a good approach would involve what you mentioned, and where
two define_index entries would produce something like what I pastie'd
above.

define_index do # index ..._core_00
define_source do #source ...core_0
# ...
end
define_source do #source ...core_1
# ...
end
end


define_index do # index ..._core_01
define_source do #source ...core_2
# ...
end
define_source do #source ...core_3
# ...
end
end


Sphinx seems to have no issue handling this. The only issue is where
you have two indexes that try to write to the same file ("path =" in
conf). Then it expects each index to have the same number of columns--
which bombs the indexer tool.

I'm digging around the source code to see where all of these
operations happen--then I'll see about throwing some changes into TS
to support the above. Alternatively, I'd maybe like to add an entry
to name the index, such as:

define_index table_index_00 do
# ..

define_index table_index_01 do
# ..

and finally:

define_index do
add_index table_index_00
add_index table_index_01
end

But until I do something, I'm just wasting energy. I'll dig through
the thinking_sphinx source to see how hard it is to do what I'm
suggesting.

Thanks,
Scott