>>Problem is that there are combinations that I'll never care about What do u mean by the above statement.
I would definitly go for the first solution, though ur taking more time on the index, but u dont need to keep adding columns and doing joins as u think would happen in the second approch. Keep it as simple as possible On 7/7/08, Waynn Lue <[EMAIL PROTECTED]> wrote: > > I have two tables, Applications and Settings. Here are the two schemas: > > mysql> desc Applications; > > +-----------------+---------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | > Extra | > > +-----------------+---------------------+------+-----+---------+----------------+ > | ApplicationId | bigint(20) unsigned | | PRI | NULL | > auto_increment | > | ApplicationName | varchar(200) | YES | | NULL > | | > > +-----------------+---------------------+------+-----+---------+---------------- > > mysql> desc Settings; > +---------------+---------------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +---------------+---------------------+------+-----+---------+-------+ > | ApplicationId | bigint(20) unsigned | | PRI | 0 | | > | SettingId | int(10) unsigned | | PRI | 0 | | > | SettingValue | text | YES | | NULL | | > +---------------+---------------------+------+-----+---------+-------+ > > Settings is just a multi map, where I'll have things like (1, 1, "true"), > (1, 2, "short text"), (1, 3, "145"), etc. Essentially each Application has > multiple rows in Settings. > > There are values in Settings that I want to index, for example I want to > find all ApplicationIds where SettingId = 34 and SettingValue = "149951". > Here are the two methods I'm considering. > > 1. Add an index on (SettingId, SettingValue). It allows me to look for > any > arbitrary combination of SettingId, SettingValue, and it also gives me > flexibility in that any thing I add to the table, it'll automatically be > indexed. Problem is that there are combinations that I'll never care > about, > so I'm wasting space and INSERT time. > 2. Pull the values that I'm interested in out into Applications, and add > it > as a column that that table with a secondary index. I'll have to update > both Applications and Settings in a transaction, and make sure they're kept > consistent. This is more efficient, but every time I want to index > something new, I'll have to either take down Applications and add a new > column, or I'll have to create another table and INNER JOIN it to > Applications. > > Does anyone have experience building out a system that's similar? Am I > getting all the pros and cons? Also, is a VARCHAR better than a text field > in terms of indexing, or even in terms of performance? > > Thanks for any input, > Waynn >
