Waynn Lue 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
If you query the application id and then carry it around in your
application, and if InnoDB is not an objection, why not use foreign key
constraints ?
This would index your columns, without you having to worry about it,
albeit with a slight performance disadvantage, but hey, it's the 21st
century.
TABLE applications (applicationid PK, applicationname UNIQUE)
TABLE settingtypes (settingid PK, settingdescription VARCHAR?)
TABLE settings(applicationid FOREIGN KEY, settingid FOREIGN KEY, setting
VARCHAR, UNIQUE KEY(applicationid, settingid))
HTH.
Stijn
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
