Apply a dense mask to a mySQL database

[ahpah]

I would like to be able to perform queries against a large database (1M+ entries) only looking at specifically masked set of entries. I currently have the candidate entries as a packed C-style bit vector.

How do I tell mySQL to only look at the records within this dense mask?

The mask can be very dense and the last thing I want is 250,000 entries in a IN clause of a WHERE.

Any thoughts?

Thanks,

Paul

[raf]

Welcome here.

What do you mean with 'dense mask' and 'the candidate entries as a packed C-style bit vector' ?

[ahpah]

The mask is the exclusive set of entries to examine for the rest of the query.

Suppose that there are 10 users. A mask might be (1, 5, 7, 8, 9). Since these masks can be dense I want to be able to tell mySQL to only return results with ID's within that mask.

Right now I do something like

SELECT * from WHERE name='...' and email='...' and ID in (1,5,7,8,9)

With big masks this IN clause has 250,000+ entries!

Any help would be greatly appreciated.

Thanks

Paul

[raf]

Hmm. I would solve this by changing/optimizing my db-design.
When i'm drawing out my data-model, i identify the variabels that will be used frequently for searching/filtering records. All these variables are numerical (! much faster then string-variables) and indexed (!! way way faster).
I can't imagen i'd have to use large IN clauses. I mean, 1/4 of the records in an inclause? I assume these aren't handpicked so there's probably one or more variabels you can include in you condition so identify them.

I can't imagen this problem would ever arise if you've a good db-design and if you make efficient use of joins and subselects.

[ahpah]

This unfortunately can not be fixed in the way you have described. Each user has a set of people that are "visible". That visibility is not derrived from any other table. There are no other tables.

Paul

[raf]

So where are the ID's stored then? If you can only identify the records, based on the ID and nothing else, then there's no other option then an IN() clause, which is quite insane.


Why not create another table?

something like
newtableID|oldtableID|userID|

the oldtableID is the foreign key to the original table. UserID is a reference to a userstable.
For each user, you'll have a record for each record of the original table he can see. Or the 'people' you mention
newtableID|people|userID|
one row for each user - people combination
The a simple inner join would solve it.

Turning this flatfile into a relation db shouldn't be that hard.

I don't know your situation, but irt seems unlikemly to me there isn't some sort of fixed businessmodel that specifyes which user needs acces to which records.
Implementing these bussinesrules into your db-design will make it faster, more transparant, easier to maintain etc

[ahpah]

Yes but the size of this table will be 250,000x1M because I have 1M users each with an unpredictable mask of 250,000 others.

Paul

[raf]

I'm starting to see the picture. You'd sort a need a crosstab of all users on all users, but that would be way to big for any db.

I don't see a way out. You'll need to be able to categorize the users and determine visability based on the categorys.