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
