Christopher Kings-Lynne wrote:
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.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
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.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
