Christopher Kings-Lynne wrote:
> Sounds like you want a many-to-many table that maps user_ids to match_ids
>
> Then you can put an index over (user_id, match_id) and the search will
> be very fast.
>
> Chris
>
If I understand you correctly, I believe I've tried this approach. While
matching on a single attribute and a single value was indeed very fast
and used an index, as soon as I tried to match on more than one value
(where valueid in (1, 2, 3)) the index was no longer used. Since my
approach used ints, I used in(), which is effectively "or", which is
presumably why the index is no longer used. With the bit, one would do a
bitwise "or" (where value & search = value). This cannot be easily
indexed, afaik.
The other problem I had with a 1:many table, where there was a row for
every person's attributes (~20M rows) was that somehow time was lost in
either sorting or somewhere else. Individual queries against a single
attribute would be very fast, but as soon as I tried to join another
attribute, the query times got really bad. See http://sh.nu/w/email.txt
line 392 (Don't worry, there are line numbers in the page).
So far I've stuck with my original plan, which is to maintain a 1:1
table of people:attributes where each attribute is in its own column.
Still, no index is used, but it's been the best performer up to now.
I'm still looking for a better plan though.
Daniel
--
Daniel Ceregatti - Programmer
Omnis Network, LLC
You are fighting for survival in your own sweet and gentle way.
