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.


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 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 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

Reply via email to