MySQL indexes and bitwise functions

Small question.

I'm considering using an integer field and doing bitwise function to find if something is in the set. I don't want to use MySQL's SET field since the data is not known at table creation time.

The problem is, I need to search by this column.

Take a table with three fields... (id int primary auto,flags int,name varchar(50)). Index on the flags field.

Data:
1,1,'One',
2,3,'Two,
3,2,'Three',
4,5,'Four'

Now if I do a query: 'SELECT name FROM table WHERE flags & 1 = 1' Will this use the index on the flags field?

I know that normalization says to store the flags in a separate table, but I'm not about to make tons of lookup tables that will grow exponentially.

 

It won't.

Here's a useful link

 

I'm at a loss on what todo in this situation, then.

The purpose is that I have multiple sites and I want to have a single content table in the database for everything. My goal is to be able to say... Article with id 4 gets posted on siteids (1,3). So that is why I'd use the bitwise for the siteids.

I don't want to do lookup tables since I would have to make many entires in the table just to post on multiple sites, plus I'd have to add another table when selecting the content out of the database.

 

But that's the right way to do it.

Articles: ID, content...
Sites: ID...
Articles_to_Sites: ArticleID, SiteID

You can't really have "too many" entries.

 

Well, I'm trying to not have to do multiple insert queries on every thing I post.

If I posted an article, I would need to insert stuff into the lookup table. It isn't just database layout I'm looking at... it is speed optimization. Adding that lookup table will increase the time it takes for many things (such as counting how many articles for pagination and such).

There is really good way to accomplish what I'm looking for without sacrificing speed somewhere.

 

I don't think so. You can count articles in the lookup table. With 2 integer fields, I can't imagine why counting that would be slower than counting articles in the main article table.

Tip: You shouldn't "count" articles for pagination. Add another field in the "Sites" table, for example: "num_articles" and increase/decrease that counter after inserting/deleting articles. You can do that using triggers.

 

I mean more complex things like when a person searches for articles with a specific categoryid. Or multiple categories.