I have a messages table with each message sent to the lists. I have a threads table with the subject of those messages with the one to many link. Each subject is a varchar. I currently have the subject at a length of 255 for the reason Maureen gave above.
The question goes to any performance difference between varchar(255), varchar(300), varchar(400), etc. Is there some esoteric difference between the sizes. I remember reading an article recently about how one datatype (like smalldatetime or tinyint) was less efficient than it's 'bigger' brother (datetime or int). I should look for the article, but the point is, under the hood there may be differences in how a varchar field is indexed or searched based on its size. I assume there is no real difference and it's just me going off on a tangent again. :) -- Michael Dinowitz On Thu, May 6, 2010 at 2:10 AM, Andrew Scott <andr...@andyscott.id.au> wrote: > > I am not sure how you mean by storing email subjects, if you are referring > to a list of emails then I would be more inclined to do a one to many > relationship with another table. The benefits is faster indexing to begin > with. > > But that is an assumption because you are not very clear what the column is > storing. As for optimal size, I doubt there is really such a thing because > if you set it to its max size based on the page size, you could still end up > needing more again. I know that might be unlikely because even at the lowest > page size the max for varchar is 4096. > > Hope that helps some what. > > > On Thu, May 6, 2010 at 10:19 AM, Michael Dinowitz < > mdino...@houseoffusion.com> wrote: > >> >> That was my standard reasoning when setting varchar fields but if I'm >> storing email subjects then there may be more than 255 characters. Why limit >> myself to a standard that has little to do with what I'm actually doing. >> >> To add to my question, is there an optimal varchar size for >> indexing/searching? >> >> Thanks >> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3344 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
